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

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

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

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

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

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