CODEWITHSUNDEEP
excelexcel-2007
ThEvensen
ThEvensen

Reputation: 69

SUMIFS and multiple lookups within array

I'm trying to use the SUMPRODUCT formula to get the sum of money (O - number) from specific weeks (C - date) and rooms (E - text) when used by organizer (A - text). 

=SUMPRODUCT(--(A2:A3000=S2);--(E2:E3000=R2);--(WEEKNUM(C2:C3000)=3);O2:O3000)

This works well when looking up one organizer at a time.

How can I modify this formula to look up several organizers at once? Organizers are listed in S2:S10.

I'm thinking something like the following:

=SUMPRODUCT(--(A2:A3000=S2 OR S3 etc.);--(E2:E3000=R2);--(WEEKNUM(C2:C3000)=3);O2:O3000)

Also - Can this be done in an easy and neat way with the faster SUMIFS?

Upvotes: 2

Views: 1518

Answers (1)

barry houdini
barry houdini

Reputation: 46331

You can use MATCH like this

=SUMPRODUCT(--ISNUMBER(MATCH(A2:A3000;S2:S10;0));--(E2:E3000=R2);--(WEEKNUM(C2:C3000+0)=3);O2:O3000)

For SUMIFS you'll need a helper column for the week number......

With week number in column Z try

=SUMPRODUCT(SUMIFS(O:O;A:A;S2:S10;E:E;R2;Z:Z;3))

Assumes no repeats in S2:S10 or you'll double count. Note that SUMPRODUCT merely sums the array created by SUMIFS (an array of nine values because of S2:S10) - that latter function does the hard work so it should be faster

BTW I'm not sure your original formula will work because WEEKNUM doesn't normally accept a range....but it does except an array hence the +0 in my version.....

Upvotes: 2

Related Questions