Add across sheets by criteria
 

I have multiple sheets with 3 columns of data each. The first col is ID
info, the other two track hours and dollars, respectively. I want to add up
all the numbers in all the sheets in col B(hours) that match each particular
ID . . . and the IDs do NOT always come in the same rows. I want to do the
same for the col C(dollars).
My goal is a summary sheet that lists each and every ID with total hours and
total dollars for each.

Add across sheets by criteria
 

Assume that in summary sheet you are having the headers in 1st Row

Sheet Name: Summary
A1 : Id
B1 : Hours
C1 : Dollars

Select the B Column and do right
ClickFormatNumberCategoryCustomTypeCop y and paste the below
format:-
[h]:mm
And give Ok€¦

Put this formula in B2 cell:-
=SUMIF(Sheet1!$A:$A,SUMMARY!$A2,Sheet1!B:B)+SUMIF( Sheet2!$A:$A,SUMMARY!$A2,Sheet2!B:B)+SUMIF(Sheet3! $A:$A,SUMMARY!$A2,Sheet3!B:B)

Copy the B2 cell and paste it in C2 cell which will look like this:-

=SUMIF(Sheet1!$A:$A,SUMMARY!$A2,Sheet1!C:C)+SUMIF( Sheet2!$A:$A,SUMMARY!$A2,Sheet2!C:C)+SUMIF(Sheet3! $A:$A,SUMMARY!$A2,Sheet3!C:C)

In the above formula I have given three sheets add some more Sumif if you
need to include it for some more sheets.

Remember to Click Yes, if this post helps!

--------------------
(Ms-Exl-Learner)
--------------------


"sbrodginski" wrote:

I have multiple sheets with 3 columns of data each. The first col is ID
info, the other two track hours and dollars, respectively. I want to add up
all the numbers in all the sheets in col B(hours) that match each particular
ID . . . and the IDs do NOT always come in the same rows. I want to do the
same for the col C(dollars).
My goal is a summary sheet that lists each and every ID with total hours and
total dollars for each.

Add across sheets by criteria
 

Hi,

Assuming that you have to bring data from 3 sheets - sheet1, sheet2 and
sheet3, you may use the following formula to sum data in range D4:D8 of each
sheet where the value in range C4:C8 of each sheet is equal to the value
mentioned in cell C4 (ID number) of the summary sheet

SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1: 3"))&"!C4:C8"),C4,INDIRECT("Sheet"&ROW(INDIRECT("1 :3"))&"!D4:D8")))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"sbrodginski" wrote in message
...
I have multiple sheets with 3 columns of data each. The first col is ID
info, the other two track hours and dollars, respectively. I want to add
up
all the numbers in all the sheets in col B(hours) that match each
particular
ID . . . and the IDs do NOT always come in the same rows. I want to do
the
same for the col C(dollars).
My goal is a summary sheet that lists each and every ID with total hours
and
total dollars for each.

Add across sheets by criteria
 

If there are only a "few" sheets you can use an array constant.

Replace:

ROW(INDIRECT("1:3"))

With:

{1,2,3}

--
Biff
Microsoft Excel MVP


"Ashish Mathur" wrote in message
...
Hi,

Assuming that you have to bring data from 3 sheets - sheet1, sheet2 and
sheet3, you may use the following formula to sum data in range D4:D8 of
each sheet where the value in range C4:C8 of each sheet is equal to the
value mentioned in cell C4 (ID number) of the summary sheet

SUMPRODUCT(SUMIF(INDIRECT("Sheet"&ROW(INDIRECT("1: 3"))&"!C4:C8"),C4,INDIRECT("Sheet"&ROW(INDIRECT("1 :3"))&"!D4:D8")))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

"sbrodginski" wrote in message
...
I have multiple sheets with 3 columns of data each. The first col is ID
info, the other two track hours and dollars, respectively. I want to add
up
all the numbers in all the sheets in col B(hours) that match each
particular
ID . . . and the IDs do NOT always come in the same rows. I want to do
the
same for the col C(dollars).
My goal is a summary sheet that lists each and every ID with total hours
and
total dollars for each.