help with sum if

[Trixie]

I need help with sum if. I have 5 sheets on a workbook, the 1st sheet has 12
names. The other 4 sheets contain the same 12 names in Column A and and
weekly amounts for 52 weeks in rows. I am looking to see if the name on
sheet 5 matches on the other sheets then sum the weekly amounts just for that
name on sheet 5. Can anyone help?

 

[Sandy Mann]

Assuming that the names as on Sheet 1 and the other 4 sheets have names &
Amounts so that you want the formula in Sheet 1 not sheet 5 as your post
ends up seeming to suggest, (at least to me) for the Name in Cell A8 try:

=SUMPRODUCT((Sheet2!A1:A12=A8)*(Sheet2!B1:BA12)+(Sheet3!A1:A12=A8)*(Sheet3!B1:BA12)+(Sheet4!A1:A12=A8)*(Sheet4!B1:BA12)+(Sheet5!A1:A12=A8)*(Sheet5!B1:BA12))

If you do want the formulas in Sheet5 then use:

=SUMPRODUCT((Sheet1!A1:A12=A8)*(Sheet1!B1:BA12)+(Sheet2!A1:A12=A8)*(Sheet2!B1:BA12)+(Sheet3!A1:A12=A8)*(Sheet3!B1:BA12)+(Sheet4!A1:A12=A8)*(Sheet4!B1:BA12))

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Trixie]

I'm trying that one, but it's trying to Update Values...and reference another
workbook. Here's my main sheet. I would like to find D71 in the
corresponding sheets and sum the rows for just D71. One sheet is 2005, one
is 2006, so on. 2005 has 435 rows, each time it sees D71 in column A, I want
it to go to column J and add that number. I hope it's a little clearer.

Year 2005 Year 2006 Year 2007 Year 2008
D71-
D76 -
GLX -
LQQ -
Thank you for your help.
Trixie

 

[Sandy Mann]

I think that it is trying the reference another Workbook because youd on't
have the sheets that I am quoting. With your sheets named:

Year 2005 Year 2006 Year 2007 Year 2008

and in the other sheet, (the sheet where you want the summing to occur),the
*names* that you want to lookup starting with D71 in A2 being:

A2 D71
A3 D76
A4 GLX
A5 LQQ

Enter the following formula:

=SUMPRODUCT(('Year 2005'!$A$1:$A$435=A2)*('Year 2005'!$J$1:$J$435)+('Year
2006'!$A$1:$A$435=A2)*('Year 2006'!$J$1:$J$435)+('Year
2007'!$A$1:$A$435=A2)*('Year 2007'!$J$1:$J$435)+('Year
2008'!$A$1:$A$435=A2)*('Year 2008'!$J$1:$J$435))

and drob it down to A5 on the fill handle.

This will add up every cell in Column J that has D71 in the same row in
Column A in the sheets: Year 2005, Year 2006, Year 2007 & Year 2008


--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

No wonder people get confused by my posts!

and drob it down to A5 on the fill handle.

was meant to be:

and drag down to Row 5 on the fill handle.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Trixie]

worked perfectly.
Thank you
Trixie

No wonder people get confused by my posts!


was meant to be:

and drag down to Row 5 on the fill handle.

--
HTH

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk

 

[Sandy Mann]

Glad that we both got there in the end. Thanks for the feedback.

--

Sandy
In Perth, the ancient capital of Scotland
and the crowning place of kings

(e-mail address removed)
Replace @mailinator.com with @tiscali.co.uk