Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
I am using excel 2003 and having issues with this formula. What I need is a
count of how many rows contain both condition one from column A and condition two from column b. The formula I have right now is: =SUMPRODUCT(--(A1:A5000=C1),--(B1:B5000=C2) C1 and C2 are my two conditions. Now sometimes I can easily just auto filter the data and count it and then manually type it but sometimes it is too large to do this. The reason I mention this is in one case once filtered there is only 5 rows that match both conditions but the formula returns 518. So I am wondering if this is the right formula to find what I am truly looking for cause it sounds to me that I almost need a couple COUNTIF's with a & in the middle but I can not get that to work either. Thanks for any help |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
If you are searching for text values and one of those columns (eg B)
could contain blanks, then you might have to do this: =SUMPRODUCT(--(A1:A5000=C1),--(B1:B5000=C2),--(B1:B5000<"")) Hope this helps. Pete On Feb 26, 8:16*pm, Chad Portman wrote: I am using excel 2003 and having issues with this formula. What I need is a count of how many rows contain both condition one from column A and condition two from column b. The formula I have right now is: =SUMPRODUCT(--(A1:A5000=C1),--(B1:B5000=C2) C1 and C2 are my two conditions. Now sometimes I can easily just auto filter the data and count it and then manually type it but sometimes it is too large to do this. The reason I mention this is in one case once filtered there is only 5 rows that match both conditions but the formula returns 518. So I am wondering if this is the right formula to find what I am truly looking for cause it sounds to me that I almost need a couple COUNTIF's with a & in the middle but I can not get that to work either. Thanks for any help |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
You are on the right track, you may need to force the formula to multiply the
arrays. (I'm assuming you accidentally left off a parenthesis, as your formula you posted is not valid) Try: =SUMPRODUCT((A1:A5000=C1)*(B1:B5000=C2)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Chad Portman" wrote: I am using excel 2003 and having issues with this formula. What I need is a count of how many rows contain both condition one from column A and condition two from column b. The formula I have right now is: =SUMPRODUCT(--(A1:A5000=C1),--(B1:B5000=C2) C1 and C2 are my two conditions. Now sometimes I can easily just auto filter the data and count it and then manually type it but sometimes it is too large to do this. The reason I mention this is in one case once filtered there is only 5 rows that match both conditions but the formula returns 518. So I am wondering if this is the right formula to find what I am truly looking for cause it sounds to me that I almost need a couple COUNTIF's with a & in the middle but I can not get that to work either. Thanks for any help |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Luke M you are my hero. I love you so much right now.
"Luke M" wrote: You are on the right track, you may need to force the formula to multiply the arrays. (I'm assuming you accidentally left off a parenthesis, as your formula you posted is not valid) Try: =SUMPRODUCT((A1:A5000=C1)*(B1:B5000=C2)) -- Best Regards, Luke M *Remember to click "yes" if this post helped you!* "Chad Portman" wrote: I am using excel 2003 and having issues with this formula. What I need is a count of how many rows contain both condition one from column A and condition two from column b. The formula I have right now is: =SUMPRODUCT(--(A1:A5000=C1),--(B1:B5000=C2) C1 and C2 are my two conditions. Now sometimes I can easily just auto filter the data and count it and then manually type it but sometimes it is too large to do this. The reason I mention this is in one case once filtered there is only 5 rows that match both conditions but the formula returns 518. So I am wondering if this is the right formula to find what I am truly looking for cause it sounds to me that I almost need a couple COUNTIF's with a & in the middle but I can not get that to work either. Thanks for any help |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Your formula is correct (other than the missing parenthesis at the end).
One reason you may see a difference between the formula and the AutoFilter, is if you have numbers stored as text. SUMPRODUCT will consider the number 123 different than the text string "123". While AutoFilter will treat them the same. Another thing to watch out for when using AutoFilter, is if you also have Freeze Panes activated. The filtered results won't always be scrolled to the top of the list. So, when the filter is applied, you may only see 5 rows visible, but more will be revealed if you scroll up. HTH Elkar "Chad Portman" wrote: I am using excel 2003 and having issues with this formula. What I need is a count of how many rows contain both condition one from column A and condition two from column b. The formula I have right now is: =SUMPRODUCT(--(A1:A5000=C1),--(B1:B5000=C2) C1 and C2 are my two conditions. Now sometimes I can easily just auto filter the data and count it and then manually type it but sometimes it is too large to do this. The reason I mention this is in one case once filtered there is only 5 rows that match both conditions but the formula returns 518. So I am wondering if this is the right formula to find what I am truly looking for cause it sounds to me that I almost need a couple COUNTIF's with a & in the middle but I can not get that to work either. Thanks for any help |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
SUMPRODUCT | Excel Worksheet Functions | |||
Help with SUMPRODUCT please : ) | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions | |||
sumproduct | Excel Worksheet Functions |