How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site. Ask follow up questions if you need to.
  • Go back-and-forth until satisfied
    Rate the answer you receive.
Ask Jess M. Your Own Question
Jess M.
Jess M., Computer Support Specialist
Category: Computer
Satisfied Customers: 10384
Experience:  Computer Hardware & Software Specialist - 10yrs Exp, Web Designer & Developer
49766785
Type Your Computer Question Here...
Jess M. is online now

I want to count values in a table if the row value criteria

This answer was rated:

Hi, I want to count values in a table if the row value criteria (cost centre AS03) and column header (which is period, e.g. 2105) match. So the cost centre values in the rows could be repeated down the line but the header in column would be unique since it is a period
JA: Is there a specific language you're coding in?
Customer: I just need this on excel sheet
JA: What application are you using?
Customer: MS Excel
JA: Have you installed any updates recently?
Customer: No
JA: Anything else you want the Microsoft Office Expert to know before I connect you?
Customer: No thanks

Hi, welcome and thank you for your question. My name is ***** ***** I am glad to assist you today.

I am very sorry about your issues.Can you send me a sample Excel file with few data on it to work on?

Best regards,

Jess

Customer: replied 9 days ago.
File attached (3263ZS2)
Customer: replied 9 days ago.
Hi please see the attached file thanks
Customer: replied 9 days ago.
I am not sure what formula would help, do not want to use Pivot for this
Customer: replied 9 days ago.
is there a way to use countifs or sumproduct etc. for this

Thank you. Please give me a moment to check your file.

Here is your needed formula:

=COUNTIFS($A$2:$A$7,A12,$B$2:$B$7,"<>")

Please note that the ranges A2:A7 and B2:B7 are locked so that you can drag down the formula without changing the range.

See the attached file.

Customer: replied 9 days ago.
thanks but would it work for other columns? because I would need to select the range for each column I think?
Customer: replied 9 days ago.
is there a way it can look up the value in the column header and then select the data if it is in right column too, or would I need to select range every time

Yes it should. I made the first formulas for you. Please try the same approach on the other columns. I will try to do them for you. But it is good for you to try it yourself as well.

Customer: replied 9 days ago.
okay I see , I have tried now thanks , got a bit confused initially
Customer: replied 9 days ago.
Thank you :)
Customer: replied 9 days ago.
I removed the $ sign so it goes to the relative column basically - is that right?

Here is the completed file. Please check the locks made in the first formula. It is working now.

Jess M. and 8 other Computer Specialists are ready to help you
Customer: replied 9 days ago.
great that is what I thought :) thanks a lot for all your help

You're welcome and thank you for using our service.

Best regards,

Jess