If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Sum Multiple Matches
Is there a way to sum totals based on multiple matches? Say in the following
example, from a separate sheet, i want to sum up the totals for stationary on the first sheet. e.g. Expense Type Total stationary £15 car £140 instruments £150 stationary £25 ....so I want to return the 15+25 based on a lookup (assume lookup value to be a given/stated i.e. "stationary"). Range will be constant size and named e.g. "JanExpenses" N.b. there will be 12 sheets, each with similar lists (1 for each month) which i would then need to sum, so the above might just be for January. I would then want to add this to Feb-Dec returned totals thanks |
#2
|
|||
|
|||
Sum Multiple Matches
Hi,
Something along these lines =SUMPRODUCT((Sheet2!A2:A5="Stationary")*(Sheet2!B2 :B5)) Mike "Mart" wrote: Is there a way to sum totals based on multiple matches? Say in the following example, from a separate sheet, i want to sum up the totals for stationary on the first sheet. e.g. Expense Type Total stationary £15 car £140 instruments £150 stationary £25 ...so I want to return the 15+25 based on a lookup (assume lookup value to be a given/stated i.e. "stationary"). Range will be constant size and named e.g. "JanExpenses" N.b. there will be 12 sheets, each with similar lists (1 for each month) which i would then need to sum, so the above might just be for January. I would then want to add this to Feb-Dec returned totals thanks |
#3
|
|||
|
|||
Sum Multiple Matches
In each sheet in same cell (say Z1)
=SUMIF(A1:A100,"stationary",B1:B100) On summary sheet =SUM('Sheet1:Sheet10'!Z1) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mart" wrote in message ... Is there a way to sum totals based on multiple matches? Say in the following example, from a separate sheet, i want to sum up the totals for stationary on the first sheet. e.g. Expense Type Total stationary £15 car £140 instruments £150 stationary £25 ...so I want to return the 15+25 based on a lookup (assume lookup value to be a given/stated i.e. "stationary"). Range will be constant size and named e.g. "JanExpenses" N.b. there will be 12 sheets, each with similar lists (1 for each month) which i would then need to sum, so the above might just be for January. I would then want to add this to Feb-Dec returned totals thanks |
#4
|
|||
|
|||
Sum Multiple Matches
thanks Mike , that's perfect.
I think Bernard's suggestion would have meant additional summing on the individual sheets so the Sumproduct option is ideal. ...just glad i don't need to use Index match type solution ! "Mike H" wrote: Hi, Something along these lines =SUMPRODUCT((Sheet2!A2:A5="Stationary")*(Sheet2!B2 :B5)) Mike "Mart" wrote: Is there a way to sum totals based on multiple matches? Say in the following example, from a separate sheet, i want to sum up the totals for stationary on the first sheet. e.g. Expense Type Total stationary £15 car £140 instruments £150 stationary £25 ...so I want to return the 15+25 based on a lookup (assume lookup value to be a given/stated i.e. "stationary"). Range will be constant size and named e.g. "JanExpenses" N.b. there will be 12 sheets, each with similar lists (1 for each month) which i would then need to sum, so the above might just be for January. I would then want to add this to Feb-Dec returned totals thanks |
#5
|
|||
|
|||
Sum Multiple Matches
Mart,
Your welcome and thanks for the feedback Mike "Mart" wrote: thanks Mike , that's perfect. I think Bernard's suggestion would have meant additional summing on the individual sheets so the Sumproduct option is ideal. ..just glad i don't need to use Index match type solution ! "Mike H" wrote: Hi, Something along these lines =SUMPRODUCT((Sheet2!A2:A5="Stationary")*(Sheet2!B2 :B5)) Mike "Mart" wrote: Is there a way to sum totals based on multiple matches? Say in the following example, from a separate sheet, i want to sum up the totals for stationary on the first sheet. e.g. Expense Type Total stationary £15 car £140 instruments £150 stationary £25 ...so I want to return the 15+25 based on a lookup (assume lookup value to be a given/stated i.e. "stationary"). Range will be constant size and named e.g. "JanExpenses" N.b. there will be 12 sheets, each with similar lists (1 for each month) which i would then need to sum, so the above might just be for January. I would then want to add this to Feb-Dec returned totals thanks |
#6
|
|||
|
|||
Sum Multiple Matches
Hi Bernard, I Really like Your alternative solution here!!! Just as a General Comment here; This is exactly why this Website is so BRILLIANT, I have been looking for a solution for a similar puzzle for days on end, and the answers are just there when You need them, Totally 100% Fantastic Thank You so much!! Rgds, Hilvert Scheper "Bernard Liengme" wrote: In each sheet in same cell (say Z1) =SUMIF(A1:A100,"stationary",B1:B100) On summary sheet =SUM('Sheet1:Sheet10'!Z1) best wishes -- Bernard V Liengme Microsoft Excel MVP http://people.stfx.ca/bliengme remove caps from email "Mart" wrote in message ... Is there a way to sum totals based on multiple matches? Say in the following example, from a separate sheet, i want to sum up the totals for stationary on the first sheet. e.g. Expense Type Total stationary £15 car £140 instruments £150 stationary £25 ...so I want to return the 15+25 based on a lookup (assume lookup value to be a given/stated i.e. "stationary"). Range will be constant size and named e.g. "JanExpenses" N.b. there will be 12 sheets, each with similar lists (1 for each month) which i would then need to sum, so the above might just be for January. I would then want to add this to Feb-Dec returned totals thanks |
Thread Tools | |
Display Modes | |
|
|