adding another condition in sumproduct

[mattmorris]

Hi,

My current SUMPRODUCT only sums the values in the current week, how would I go about making this only the sum the values if in G12:G222 the value doesn't equal TBA, i.e adding another condition to this?

=SUMPRODUCT((Bogies!H12:H222+0>=TODAY()-WEEKDAY(TODAY())+1)*(Bogies!H12:H222+0<=TODAY()-WEEKDAY(TODAY())+7))

[Richard Buttrey]

Hi

=SUMPRODUCT((Bogies!H12:H222+0>=TODAY()-WEEKDAY(TODAY())+1)*(Bogies!H12:H222+0<=TODAY()-WEEKDAY(TODAY())+7)*(Bogies!G12:G222<>"TBA"))

But since you have XL 2010 can I suggest you should be using SUMIFS() formulae which are more efficient than the old SUMPRODUCT() array formulae.

[Fotis1991]

Hi and welcome to the forum.

Did tou try?

=SUMPRODUCT((G12:G222<>"TBA")*(Bogies!H12:H222+0>=TODAY()-WEEKDAY(TODAY())+1)*(Bogies!H12:H222+0<=TODAY()-WEEKDAY(TODAY())+7))

[mattmorris]

Ok thanks that worked.
If i was to change to the SUMIFS() what would my input be to do the same thing?
Thanks my excel knowledge is a bit limited.

[Richard Buttrey]

Hi,

Looking at your original it appears that you are counting records rather than summing a range. If so I should have mentioned COUNTIFS() not SUMIFS().

So..untested

=COUNTIFS(Bogies!H12:H222,">="&TODAY()-WEEKDAY(TODAY())+1,Bogies!H12:H222,"<="&TODAY()-WEEKDAY(TODAY())+7,Bogies!G12:G222"<>"&"TBA")