Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match function...random search? | Excel Worksheet Functions | |||
How do I edit a Named Range using macro's | Excel Worksheet Functions | |||
Cannot Expand Named Range - when size of the Range exceeds | Excel Discussion (Misc queries) | |||
Counting empty cells within a range of cells | New Users to Excel | |||
Define a range based on another named range | Excel Worksheet Functions |