Count the number of instances where multiple criteria match

[kurt.l]

I am having a similar problem to others in the subforum. I am trying to count the number of instances where TRUE shows up in the 'Buy' column (AF in the formula) and there is a corresponding number on the same line in the 'Shipped 2010' (AP) column.
I have tried =SUMPRODUCT(--(AF4:AF4270="TRUE"),(AP4:AP270>0)) and =SUMPRODUCT(--(AF4:AF4270="TRUE")*(AP4:AP270>0)) but getting the #value sign. I am not sure what I am doing wrong. HELP!

Buy Shipped 2010
FALSE 1
FALSE -
FALSE -
FALSE 12
TRUE 6

[Fotis1991]

Maybe?

=SUMPRODUCT((AF4:AF4270="TRUE")*(AP4:AP4270>0))

[Moo the Dog]

Try this:

=SUMPRODUCT((AF4:AF4270=TRUE)*(AP4:AP4270>0))

- Moo

* Looks like Fotis punched it in before I could... but I thought I would also add the difference between TRUE (a formula result) and "TRUE" (a text string).

[kurt.l]

Thanks for the fast response.

I get 0 as the answer in that case. Column AP is pulling in values using a vlookup, which I thought perhaps could be the issue, but I tried copying and pasting values, but I still get 0.

[kurt.l]

Thanks Moo and Fotis, that did it.

It was 'true', it was looking for the formula result, not a text string.

[Moo the Dog]

Glad you got it figured out.

- Moo

[kurt.l]

Well.....almost.
Strange thing is I did a =COUNTIF(AP4:AP4270,">0") which gave me 2335,but when I add the results of =SUMPRODUCT((AF4:AF4270=TRUE)*(AP4:AP4270>0)) and =SUMPRODUCT((AF4:AF4270=FALSE)*(AP4:AP4270>0)), which should give me the total of the parts, both purchased (TRUE) and manufactured (FALSE), I get a total of 2309 - a difference of 26.

Column AF has either a TRUE or FALSE (which totals 4267) and the sum of =COUNTIF(AP4:AP4270,"=0") and =COUNTIF(AP4:AP4270,">0") = 4267, so I am stumped why I am out 26.

[jeffreybrown]

It might help if you posted your workbook as a sample to work from.

To Attach a File:

1. Click on Go Advanced
2. In the frame Attach Files you will see the button Manage Attachments
3. Click the button.
4. A new window will open titled Manage Attachments - Excel Forum.
5. Click the Browse... button to locate your file for uploading.
6. This will open a new window File Upload.
7. Once you have located the file to upload click the Open button. This window will close.
8. You are now back in the Manage Attachments - Excel Forum window.
9. Click the Upload button and wait until the file has uploaded.
10. Close the window and then click Submit.

[Moo the Dog]

Strange thing is I did a =COUNTIF(AP4:AP4270,">0") which gave me 2335,but when I add the results of =SUMPRODUCT((AF4:AF4270=TRUE)*(AP4:AP4270>0)) and =SUMPRODUCT((AF4:AF4270=FALSE)*(AP4:AP4270>0)), which should give me the total of the parts, both purchased (TRUE) and manufactured (FALSE), I get a total of 2309 - a difference of 26.

What that seems to tell me is that there are 26 cells in column AF that are neither TRUE or FALSE. Or, there are 26 cells where AF = True/False, but AP <= 0

- Moo

[Moo the Dog]

As Jeffrey stated above, if you can upload your workbook (remove any sensitive data first), we may be able to figure out the issue. Without the actual data, it is pretty hard to diagnose.

- Moo

[kurt.l]

I have attached a sample of the file.

Thanks for taking the time to look at it.

[Moo the Dog]

Your formulas in L604, M604 and N604 are incorrrect. They look at the range L5:L600, M5:M600, and N5:N600, when they should be looking at the range of L4:L599, M4:M599, N4:N599.

You must have just copied the formulas from the row above and pasted into row 604, which would account for the changed ranges - as well as the differing totals.

- Moo

[kurt.l]

Thanks Moo, looked at the formulae so many times, yet I missed it repeatedly.

Thanks again for all your help.

[Moo the Dog]

You're quite welcome. Glad to help.

- Moo