Excel XP and forumula using =COUNTIF with AND

[berenger123]

I don't know if this can be done, but I want 2 conditions used in a COUNTIF statement. Ex: =COUNTIF(A1:A10,"=TRUE)AND(B10:B20,"=Accounting")

Is there a way to make a statement similar to this work? I can get either statement to work separately, but not compoundly!

 

[acw]

Hi

Countif won't do it but SUMPRODUCT will.

=SUMPRODUCT(--(A1:A10,"=TRUE),--(B10:B20,"=Accounting"))

Tony

 

[Greg Truby]

=SUMPRODUCT((A1:A10)*(B1:B10="Accounting"))

Welcome to the board and search for SUMPRODUCT and you'll find magic you never thought possible.

{Edit}In addition to not being on the same rows, your ranges are not the same size. So I think I misunderstood this at first pass. The formula above would count instances where A=TRUE AND B="Accounting" on the same (relative) row (the rows don't have to be the same, but the "offsets" are, first & first, second & second and so forth). If you want to count up all the cases where A=TRUE and B = "Accounting", period (relationship between A and B is irrelevant) then =SUMPRODUCT((A1:A10)+(B1:B10="Accounting")) {/Edit}

 

[acw]

Oops

Should be
=SUMPRODUCT(--(A1:A10=TRUE),--(B10:B19="Accounting"))
or
=SUMPRODUCT(--(A1:A10=TRUE),--(B11:B20="Accounting"))

The range sizes must be the same and syntax mistype.

Tony

 

[berenger123]

This is great! I never would have throught of the SUMPRODUCT expression. Thanks for saving so much time