Excel 2007: complex COUNTIFS()
=SUMPRODUCT(--(H3:H25-G3:G25=5)*(I3:I25="Excellent"))
Just keep the operators all the same. Try it like this:
=SUMPRODUCT(--(H3:H25-G3:G25=5),--(I3:I25="Excellent"))
I see... is there an advantage to using multiple arrays like you described?
(i.e. is it computationally faster?)
Excel 2007 has a lot of rows! The SUMPRODUCT function will calculate *every*
cell referenced. So if you reference a lot of empty unused cells you're just
wasting resources.
Yeah, that would be inefficient. :) Any chance that I could reference a
cell containing the last row to check? Something like
=SUMPRODUCT(--(H3:H&Z1-G3:G&Z1=5,...)
where Z1 would contain the last row number to check? I think I might be
setting my hopes too high this time.
Thanks for all your help,
--David
|