HOW to Evaluate a range with IF ?

[Guest]

Hi,

I try to get a formula that search in a range A1:A5 which elements are =
100% and base on that search in B1:B5 which elements are = " P1" and return
the number of elements that meat this condition.

This is the formula that I try to use and it does not work.

=SUM(IF($A1:$A6=C1,IF($B1:$B6=$D1,1,0)))

A B C D

1| 100% P1 100% P1
2| 100% P1 80% P2
3| 80% P3
4| 100% P5
5| 100% P2
6| 30% P1

What I spect is 2.

I am big time stuck and I have presentation this week

Thanks for your help

 

[Guest]

dancab -
Use SUMPRODUCT()...

This formula copied verbatim should give what you are looking for:

=SUMPRODUCT(--(A1:A6=C1),--(B1:B6=D1))

The formula you are using will work as well but you need to enter it using
Ctrl+Shift+Enter because it is an array formula.

 

[Bob Phillips]

That formula should work fine if you array enter it, that is, hit
Ctrl-Shift-Enter after typing it not just Enter.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Earl Kiosterud]

dancab,

David's SUMPRODUCT is just the thing if you need only one or two results,
like for P1 and P2. If you'll need counts for all possible column-B
entities, a pivot table, set to count, will give them to you. You won't
have to make a formula for every possible column-B value, useful especially
when you don't know in advance what values might be there in a given
circumstance.