calculation across different columns/cells

  • Thread starter Thread starter Marissa O
  • Start date Start date
M

Marissa O

I am appending an earlier question I submitted that was not complete.

I need to all the amounts in column E together based on the value i
column C.

EX if column C has product code 50 then I need it to add all amounts i
column e that go with that 50.

B C E
Q1 50 100
Q1 40 10
Q1 50 30
Q1 40 200
Q2 80 40
Q4 60 20
Q4 40 40


Q1 we bought product code 50 for 130
Q1 we bought product code 40 for 410
Q4 we bought product code 40 for 40

Can someone help me with a formula for this
 
One way, adapt to fit your column range

=SUMPRODUCT(SUMIF(INDIRECT("'"&MySheets&"'!C2:C100"),50,INDIRECT("'"&MySheet
s&"'!E2:E100")))

where MySheets is a named range (put the sheet names of all sheets in
contagious range, select the range, do insert>name>define and give it a
name, I used MySheets for effect)
Note that you have to put the names of all sheets there, obviously you can
also use the range instead of the defined name

=SUMPRODUCT(SUMIF(INDIRECT("'"&H2:H20&"'!C2:C100"),50,INDIRECT("'"&H2:H20&"'
!E2:E100")))

where H2:H20 is the range holding the sheet names
 
I need to also split it up by quarter. Is there something I can add t
the sumif formula to break it up by quarter too
 
Back
Top