HOW to Evaluate a range with IF ?
 

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

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.
--
Regards,
Dave


"dancab" wrote:

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

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)


"dancab" wrote in message
...
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

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.
--
Earl Kiosterud
www.smokeylake.com

"dancab" wrote in message
...
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