shamrin

asked on

# Using Sumproduct to count all rows which meet two conditions

Hi Experts,

I am trying to use Sumproduct to count all rows in a worksheet named 'Meets Criteria - deduped' which meet two conditions:

where the value in column A = "Y"

where the value in column E = "7036"

Here is my code:

=SUMPRODUCT(--('Meets Criteria - deduped'!E2:E4000=7036),--('Meets Criteria - deduped'!A2:A4000,"Y"))

Excel tells me I have an error. Can you help me fix this statement?

Thank-you!

I am trying to use Sumproduct to count all rows in a worksheet named 'Meets Criteria - deduped' which meet two conditions:

where the value in column A = "Y"

where the value in column E = "7036"

Here is my code:

=SUMPRODUCT(--('Meets Criteria - deduped'!E2:E4000=7036),--

Excel tells me I have an error. Can you help me fix this statement?

Thank-you!

ASKER CERTIFIED SOLUTION

membership

Create an account to see this answer

Signing up is free. No credit card required.

Oops, should have been:

=SUMPRODUCT(('Meets Criteria - deduped'!E1:E4000=7036)*('Meets Criteria - deduped'!A1:A4000="Y"))

=SUMPRODUCT(('Meets Criteria - deduped'!E1:E4000=7036)*('

No points for this...

If that 7036 is truly a text value, you may need:

=SUMPRODUCT(('Meets Criteria - deduped'!E1:E4000="7036")*('Meets Criteria - deduped'!A1:A4000="Y"))

If that 7036 is truly a text value, you may need:

=SUMPRODUCT(('Meets Criteria - deduped'!E1:E4000="7036")*

I'll start off saying I've never used SUMPRODUCT, but reading the help, I don't think it's going to do what you're looking to do. You said you're trying to "COUNT" all rows in a worksheet that have "Y" in col A and "7036" in col E...

=SUM(IF('Meets Criteria - deduped'!A:A="Y",IF('Meets Criteria - deduped'!E:E=7036,1,0),0))

using CTRL+SHIFT+ENTER

=SUM(IF('Meets Criteria - deduped'!A:A="Y",IF('Meets

using CTRL+SHIFT+ENTER

Although, this seems to get the same answer:

=SUMPRODUCT(--('Meets Criteria - deduped'!E:E=7036),--('Meets Criteria - deduped'!A:A="Y"))

=SUMPRODUCT(--('Meets Criteria - deduped'!E:E=7036),--('Mee

Sorry....using your ranges:

=SUMPRODUCT(--('Meets Criteria - deduped'!E2:E4000=7036),--('Meets Criteria - deduped'!A2:A4000="Y"))

My correction to what you had was the command before "Y" at the end.

=SUMPRODUCT(--('Meets Criteria - deduped'!E2:E4000=7036),--

My correction to what you had was the command before "Y" at the end.

sorry, COMMA, not command lol

Was there something wrong with my solution? I believe I provided a solution that matched your requirements better.

1. You required the use of SUMPRODUCT. The array*array format you selected is the old-style format (=SUM(array*array) with CSE) and SUMPRODUCT was created to replace this. SUMPRODUCT format is =SUMPRODUCT(array,array). Although it technically works, it is not the intended use of SUMPRODUCT.

2. My solution was the closest to match your code as your code only had a typo (comma should've been an equal sign).

1. You required the use of SUMPRODUCT. The array*array format you selected is the old-style format (=SUM(array*array) with CSE) and SUMPRODUCT was created to replace this. SUMPRODUCT format is =SUMPRODUCT(array,array). Although it technically works, it is not the intended use of SUMPRODUCT.

2. My solution was the closest to match your code as your code only had a typo (comma should've been an equal sign).

=SUMPRODUCT(--('Meets Criteria - deduped'!E2:E4000=7036)*('