View Single Post
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
David Aukerman[_2_] David Aukerman[_2_] is offline
external usenet poster
 
Posts: 14
Default 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