I have a two worksheets. I need to add two count if statements together. I need to have it calculate the person's name which is column c and then calculate the category total.



Example:

Elmer Smith
Merrick 1
Baldwin 1
Bellmore 1


The formula I have created is not calculating correctly. I have attached the worksheet.

Attached Files

Book1.xlsx (8.3 KB, 17 views)

Hi your example result is not consistent with the data. How to you get 5 everywhere ? You could try a Pivot Table

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Hi

I know it is not consistent. The formula is not right. I was asking if anyone knew what is wrong with the formula. Thank you for reply.

Like this ?

Attached Files

Book1.xlsx (12.1 KB, 12 views)

__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post

Alaska, I don't think you can use COUNTIF for this function. As I understand it, you want to count the rows in 2014!$B:$C where two conditions apply, a certain value in B and a certain value in C. The way you have it, using two COUNTIFs, it counts all the times the name appears in column B, and then adds to it the number of times "Merrick" appears in column C.

For this you probably need COUNTIFS....yeah, I think that works. Take a look at the attached. I left your formula in column B for comparison, and added COUNTIFS to C:E.

Attached Files

x.xlsx (9.0 KB, 8 views)

Thank you for your reply. I got it to work.

Thank you again

You can do it easily enough with:
=COUNTIF('2014'!C2:C9,$A3)+COUNTIF('2014'!B2:B9,B$ 2)
The problem with your formula was the having "A3" instead of, for example, A3.

If you want a count where only both conditions are met, try:
=SUMPRODUCT((('2014'!C2:C9)=$A3)*(('2014'!B2:B9)=B $2))

__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]

The attached spreadsheet I believe does what you want to do. Your biggest problem was using the quotes when defining the comparison cell. I removed the quotes and inserted some $ to help when you copy/paste the formula to minimize the amount of editing that will be needed.

Look at the formulas in the yellow shaded area for what I did.

Good luck.

Attached Files

Solved - 2 CountIF statements.xlsx (9.4 KB, 8 views)