SumProduct #value error

[Chuck]

Hi All,

I am sure my formula is correct but the only catch is the the arrays
dont match.

=SUMPRODUCT(--(Resources!$B$4:$B$29=Overview!B16),--(MONTH(Resources!
F3:AE3)=Overview!D9),(Resources!F4:AE29))

My spreadsheet looks like

Resources Tab
B4:B29 = Team 1, team 2, team 3 etc to team 26 (maybe duplicate teams
in this row)
F33 = months of the year (7, 8, 9, 10, 11, 12, 1, 2, 3, 4, 5, 6),
unique numbers
F4:Q29 = random numbers.. this array is what i want to calculate

Overview Tab
B16:B39 = 1st criteria match against B4:B29
E15:O15 = 2nd criteria match against F33
E16:O39 = where sumproduct would populate results based on criteria 1
& 2 from F4:Q29

currently, from my understanding, SumProduct requires same array
dimensions, but my arrays dont fit that criteria. Can someone advise
how to rectify this? I am getting the #value error

Any help would be great
Cheers

 

[Dave Peterson]

Try this syntax:

=SUMPRODUCT((Resources!$B$4:$B$29=Overview!B16)
*(MONTH(Resources!F3:AE3)=Overview!D9)
*(Resources!F4:AE29))

This will fail if any of those ranges contain text. (The -- syntax ignores text
(treating it as 0).)

Bob Phillips explains =sumproduct() in much more detail here:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

And J.E. McGimpsey has some notes at:
http://mcgimpsey.com/excel/formulae/doubleneg.html