q; SUMIF how?

J

JIM.H.

How to resolve this:
I think I need to implement SumIf here but not clear how, here is what I
need to do:
Sheet1:
A Column: 10,10,10,11,11,11,12,12
B Column: 100,101,110,120,130,140,100,100
C Column: $50, $25, $15, $5, $25, $20,$30,$10

Sheet2:

I want to give numbers in B column in Sheet1 as a parameter and sum C column
in Sheet1 based on A column:
So
A1: 100 (one number in B Column in Sheet1)
B Column: 10,11,12 (distinct values)
C Column; $50,empty,$40 (sums)

How can I do this?
 
S

Sandy Mann

Try:

=SUMPRODUCT((Sheet1!$A$1:$A$8=B1)*(Sheet1!$B$1:$B$8=$A$1)*Sheet1!$C$1:$C$8)




--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

[email protected]
Replace @mailinator.com with @tiscali.co.uk
 
R

Roger Govier

Hi Jim

On sheet2 in cell C1 enter
=SUMPRODUCT(--(Sheet1!$B$1:$B$8=Sheet2!$A$1),
--(Sheet1!$A$1:$A$8=Sheet2!B1),Sheet1!$C$1:$C$8)
and copy down
 
J

JIM.H.

Hi Sandy,
Thanks for reply, but why am I multiplying them, I just need to sum C column
based on the criteria on A and B columns.
 
P

Peo Sjoblom

Why don't you try the formula first to see if it works? You are multiplying
the arrays to convert Boolean values into zeros and ones then you are
summing the values in C based on that. Think of it as a SUMIF with multiple
criteria and ranges
 
Top