Nested Countif

[joshpub]

I have a spreadsheet with 2 columns

A B
we 6
re 4
re 3
we 6
we 5

How can I do a count of B = 6 and A= we

 

[T. Valko]

Try this:

=SUMPRODUCT(--(A1:A5="we"),--(B1:B5=6))

 

[joshpub]

The formula worked I think but no matter the conditions I put it returns the
same answer

 

[joshpub]

WOULD IT HAVE MADE A DIFFERENCE IF THE COLUMNS ARE TEXT?

A B
We open
Re closed
Re open
We closed
We open

 

[T. Valko]

Just make sure you enclose the TEXT criteria in quotes:

=SUMPRODUCT(--(A1:A5="we"),--(B1:B5="open"))

Or, use cells to hold the criteria then you don't have to worry about
quotes:

D1 = we
E1 = open

=SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1))

Also note that with SUMPRODUCT you can't use entire columns as range
references unless you're using Excel 2007.

 

[joshpub]

Thank you that worked perfect

Just make sure you enclose the TEXT criteria in quotes:

=SUMPRODUCT(--(A1:A5="we"),--(B1:B5="open"))

Or, use cells to hold the criteria then you don't have to worry about
quotes:

D1 = we
E1 = open

=SUMPRODUCT(--(A1:A5=D1),--(B1:B5=E1))

Also note that with SUMPRODUCT you can't use entire columns as range
references unless you're using Excel 2007.

 

[T. Valko]

You're welcome. Thanks for the feedback!