Countif calculation based on cell values

[creative999]

Im trying to do a countif based on a few criteria. The existing formulae works fine (below), but i now need to add a new condition which basically says: If A1 = "small" then count DATA!D:D. If A1 = "large" then count DATA!E:E

C1=COUNTIFS(DATA!A:A,"ABC",DATA!B:B,Z1,DATA!C:C,"TESTING)

 

[Joe4]

Im trying to do a countif based on a few criteria. The existing formulae works fine (below), but i now need to add a new condition which basically says: If A1 = "small" then count DATA!D:D. If A1 = "large" then count DATA!E:E

C1=COUNTIFS(DATA!A:A,"ABC",DATA!B:B,Z1,DATA!C:C,"TESTING)

Seems to me like that should be two separate COUNTIFS functions, added together.

 

[creative999]

Sorry not sure i follow. Could you provide an example?

 

[Joe4]

If A1 = "small" then count DATA!D:D

Do a COUNTIFS function to get this count.

If A1 = "large" then count DATA!E:E

Do a separate COUNTIFS function to get this count.

Then add the two together, i.e.

=COUNTIFS(first rule) + COUNTIFS(second rule)

 

[creative999]

I need this to be a one or the other, not both

 

[Joe4]

I need this to be a one or the other, not both

Yes, and that is exactly what that should do.
Based on your "A1" criteria, there is no way it can be "both", so there is no risk in double-counting anything.
Have you at least tried it before discrediting it?

 

[creative999]

Yes. Stripped the formulae back to just these two conditions and get a VALUE error:

=COUNTIFS(B4,"OPEN",DATA!G:G,D4)
+COUNTIFS(B4,"CLOSED",DATA!Z:Z,D4)

 

[Joe4]

Within each COUNTIFS formula, your various criteria ranges must be the same size (see: COUNTIFS function - Microsoft Support).
B4 is a single cell and G:G is a whole column, so they are NOT the same size.

But now I think I have a better idea of exactly what you are trying to do, and I think this is what you want:

=IF(B4="OPEN",COUNTIF(DATA!G:G,D4),0) + IF(B4="CLOSED",COUNTIF(DATA!Z:Z,D4),0)