SUMPRODUCT OR COUNT(IF ..... IF(

[Artorius]

Two columns of data. Column A = PRODUCT TYPE, COLUMN E = Region

I have a spreadsheet that needs to calculate how many "Onions" (in column A) were sold in say "South West" (in Column E).

I thought I could use....

=SUMPRODUCT(A:A="Onions")*(E:E="South West")

but that does not work.

I then tried .....

{=COUNT(IF(A:A="Onions"),IF(E:E="South West))}

but that failed too.

Any ideas? Thanks.

 

[SteveO59L]

Try a Pivot Table

 

[Artorius]

Ideally I need to do this without a Pivot table ..... any idea what formula I can use?

 

[Domenic]

SUMPRODUCT does not accept whole column references. Try something like...

=SUMPRODUCT(--(A2:A100="Onions"),--(E2:E100="South West"))

Adjust the range accordingly.

Hope this helps!

Edit: Corrected the formula...

 

[NBVC]

=SUMPRODUCT(--(A1:A1000="Onions"),--(E1:E1000="South West"))

Sumproduct doesn't allow full column referencing.....

 

[Artorius]

Excellent formula, just what I needed. Many thanks ........

 

[Artorius]

Is there any limitation to SUMPRODUCT? I'm wondering if I had 3 columns of data instead of 2 ... if I could do the following?

COLUMN A = Type
COLUMN E = Region
COLUMN F = QUESTION (Y/N)

So.. if I wanted Onions, South West & Y ....

=SUMPRODUCT(--(A1:A1000="Onions"),--(E1:E1000="South West")),(--(F1:F1000="Y"))

I tried it but could not get it to work ... even tried entering as an array with the {} brackets

 

[NBVC]

You have to many parenthesis and one of them is closing off the sumproduct() part before including the 3rd argument:

Try:

=SUMPRODUCT(--(A1:A1000="Onions"),--(E1:E1000="South West"),--(F1:F1000="Y"))

 

[Artorius]

I thought I was in trouble when I started putting brackets everywhere

Amazing ... works a treat. Many thanks .....