Add across sheets by criteria

[sbrodginski]

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.

 

[Ms-Exl-Learner]

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
Click>>Format>>Number>>Category>>Custom>>Type>>Copy 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!

 

[Ashish Mathur]

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 D48 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"))&"!D48")))

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com

 

[T. Valko]

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

Replace:

ROW(INDIRECT("1:3"))

With:

{1,2,3}