Totaling cells based on input value
 

I've created an Excel spreadsheet to track monthly spending. There are 13
total worksheets -- one for each month and a summary sheet. Within each month
individual spending activities will be recorded and the summary sheet is
intended to summarize the spending year to date (YTD) by Mold Base # (MB#).
Since each individual month could have dozens of spending activities I need
to take that data and total it in the summary worksheet based on the MB#.
Within the monthly worksheets the spending is not consecutively entered by
MB# (not consecutive rows). For example, row 1 could be MB#100, row #2
MB#850, row 3 MB# 100, row 4 MB#499, row 5 MB#100. I need the summary sheet
to total spending per MB# but the entries are not consecutive.

I know this probably sounds very confusing, but I have no simpler way to
explain. I am not sure what type of equation to use. Please offer some
insight. Thanks, Jamie

Totaling cells based on input value
 

Assuming your MB# is in column A of each sheet and column B is the data you
want...
=SUMPRODUCT(('January'!A2:A100="MB#100")*('January '!B2:B100))+SUMPRODUCT(('February'!A2:A100="MB#100 ")*('February'!B2:B100))+....

And so on for each month.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jamie" wrote:

I've created an Excel spreadsheet to track monthly spending. There are 13
total worksheets -- one for each month and a summary sheet. Within each month
individual spending activities will be recorded and the summary sheet is
intended to summarize the spending year to date (YTD) by Mold Base # (MB#).
Since each individual month could have dozens of spending activities I need
to take that data and total it in the summary worksheet based on the MB#.
Within the monthly worksheets the spending is not consecutively entered by
MB# (not consecutive rows). For example, row 1 could be MB#100, row #2
MB#850, row 3 MB# 100, row 4 MB#499, row 5 MB#100. I need the summary sheet
to total spending per MB# but the entries are not consecutive.

I know this probably sounds very confusing, but I have no simpler way to
explain. I am not sure what type of equation to use. Please offer some
insight. Thanks, Jamie

Totaling cells based on input value
 

Luke,

SUMPRODUCT(('January'!A2:A100="MB#100")*('January' !B2:B100))

So the first portion of the equation (let's say January) says -- search all
entries in cells A2 thru A100 and total all that =MB#100? What does the
*('January'......) do to the equation?

Thanks, Jamie

"Luke M" wrote:

Assuming your MB# is in column A of each sheet and column B is the data you
want...
=SUMPRODUCT(('January'!A2:A100="MB#100")*('January '!B2:B100))+SUMPRODUCT(('February'!A2:A100="MB#100 ")*('February'!B2:B100))+....

And so on for each month.

--
Best Regards,

Luke M
*Remember to click "yes" if this post helped you!*


"Jamie" wrote:

I've created an Excel spreadsheet to track monthly spending. There are 13
total worksheets -- one for each month and a summary sheet. Within each month
individual spending activities will be recorded and the summary sheet is
intended to summarize the spending year to date (YTD) by Mold Base # (MB#).
Since each individual month could have dozens of spending activities I need
to take that data and total it in the summary worksheet based on the MB#.
Within the monthly worksheets the spending is not consecutively entered by
MB# (not consecutive rows). For example, row 1 could be MB#100, row #2
MB#850, row 3 MB# 100, row 4 MB#499, row 5 MB#100. I need the summary sheet
to total spending per MB# but the entries are not consecutive.

I know this probably sounds very confusing, but I have no simpler way to
explain. I am not sure what type of equation to use. Please offer some
insight. Thanks, Jamie