# Help with summing an grid with a single criteria. SUMIF or SUm(IF) or SUMPRODUCT?

1. ## Help with summing an grid with a single criteria. SUMIF or SUm(IF) or SUMPRODUCT?

Hi there,
I'm looking for a formula that will allow me to sum the number of options available in an array with a single criteria.

On the example sheet attached, I want to know the number of "ACC" styles that I am offering in the range c\$4:i\$8.
By using the filter on the "function" column, I know my answer is 5, but how can this be formulated into the yellow cells?

I have tried various versions of SUM, SUMIF and SUMPRODUCT without success and keep getting a #VALUE error.

Any help with be much appreciated!

Thank you!  Register To Reply

2. ## Re: Help with summing an grid with a single criteria. SUMIF or SUm(IF) or SUMPRODUCT?

In B1 (whose formatting needs setting to General, not Percentage):

=SUMPRODUCT((\$B\$4:\$B\$8=A14)*(\$C\$4:\$I\$8=1))

If the numbers can be greater than one in the matrix, then use this:

=SUMPRODUCT((\$B\$4:\$B\$8=A14)*(\$C\$4:\$I\$8<>"x"),\$C\$4:\$I\$8)  Register To Reply

3. ## Re: Help with summing an grid with a single criteria. SUMIF or SUm(IF) or SUMPRODUCT?

Thank you so much for your help!
the =1 was the key to what I was missing.   Register To Reply

4. ## Re: Help with summing an grid with a single criteria. SUMIF or SUm(IF) or SUMPRODUCT?  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 