q; SUMIF how?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
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

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk
 
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
 
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.
 
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
 
Back
Top