Conditional sum without changing source sheet

[ExcelStefan]

Hi!

I am trying to make a sum based on several conditions. I have an array which has a column for each day, the corresponding week number, and names as line headings which can appear several times and have to be combined.

Week 20 20 21 21 21 21 21 21 21 22 22
Bob 1 0 4 5 6 7 8 3 2 4 1
Jack 2 9 16 23 30 37 44 51 58 65 72
Bill 1 1 1 1 1 1 1 1 1 1 1
April 4 7 10 13 16 19 22 25 28 31 34
Bill 1 1 1 1 1 1 1 1 1 1 1


Until now, I use =SUMPRODUCT((C11:M11=21)*(B12:B16="Bill");C12:M16), which sums up all values done by Bill on days of week 21 (see attachement).

My problem: I need to make a condition, which will eliminate the day which is next to week 20 (Sunday), and the day which is next to 22 (Saturday). The result should be 10 instead of 14. I am not allowed to change the source table (e.g. add a line).

Does anyone have an idea, how to do this without macros?

Thanks!!

[JBeaucaire]

Try this:

=SUMPRODUCT((C11:M11=21)*(B12:B16="Bill")*(C11:M11=B11:L11)*(C11:M11=D11:N11),C12:M16)

[carsto]

=SUMPRODUCT((C11:M11=21)*(B12:B16="Bill"),C12:M16) - SUMIF(B12:B16,"Bill",INDIRECT(ADDRESS(12,MATCH(21,A11:K11,0))&":"&ADDRESS(16,MATCH(21,A11:K11,0)))) - SUMIF(B12:B16,"Bill",INDIRECT(ADDRESS(12,MATCH(21,A11:K11,1))&":"&ADDRESS(16,MATCH(21,A11:K11,1))))

however, it only works if there is actually a column heading 22 (which you sample workbook did not contain.
which means week 52 will fail if there is not a week 53!

[Marcol]

A variation of Jerrys' formula

Please Login or Register  to view this content.

Drag/Fill Down
Where
D20 Bill
D21 Bob
D22 Jack
D23 April

[JBeaucaire]

Thanks for the feedback, Stefan.

If that takes care of your need, please select Thread Tools from menu above and set this topic to SOLVED.