We help IT Professionals succeed at work.

Excel I need the Sum from a data sheet based on matching specific cells on current sheet

Mitch Swetsky
on
I have a data worksheet (Name=P01ytd) and a display sheet (Name=Dashboard (FY))
On the display sheet I have a variable number of rows with unique values in Col A.

I need to get the sum of the P01ytd Col AL where Dashboard (FY) Col A matches P01ytd Col X

I was using this vlookup (  =VLOOKUP(A12,P01Ytd!$X$1:$AL$160,15,FALSE)  ) to get the individual numbers BUT now I need the sum of all rows with values A12 to A whatever. I have a variable that I can use to replace "A whatever"

Can someone help?
Comment
Watch Question

you can use sumif to get the sum of all items basis a criteria SUMIF(range,criteria,[sum Range]) or also if several criterias you can use if you have excel 2007 sumifs(sum Range,Criteria Range1,Criteria Range2, Criteria Range3, ....)

so in your case I guess it would be something like
=SUMIF(P01Ytd!$X$1:$AL$160,VLOOKUP(A12,P01Ytd!$X$1:$AL$160,15,FALSE))

For sure easier to resolve with an example if you post it.
gowflow
Mitch SwetskyBusiness Analyst

Author

Commented:
Thanks gowflow for the quick reply and help.

I have attached a sample book with a display sheet and a P01ytd sheet
On the display sheet I need J1 to be the sum of the J rows below
Column A on Display will match Col X on the P01ytd sheet.


Book1.xls
Ardhendu SarangiSr. Project Manager
Commented:
hi,
Can you please check this? I have changed the formula in Column J to add up the totals when there is match from Display Column 1 to Column X on the P01ytd sheet.

formula used =SUMIF(P01ytd!X:X,Display!A3,P01ytd!AL:AL)

i was not sure what you are trying to get in Column K and why the cells were formatted as percentage.

- Ardhendu
Book1.xls
Mitch SwetskyBusiness Analyst

Author

Commented:
Thanks, with your suggestions, I realized that I could just sum the active cells in the column with no reference to the data sheet.