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"))
Is there a way to generalize this to include all rows from row 3 onward?
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.
=SUMPRODUCT(--(H3:H1048576-G3:G1048576=5),--(I3:I1048576="Excellent"))
If you're only using a fraction of all 1,048,576 rows the above is a huge
waste of precious resources!
--
Biff
Microsoft Excel MVP
"David Aukerman" wrote in message
...
Hi Biff,
Thanks, that helps... I was afraid that COUNTIFS couldn't do that. So if
I
understand SUMPRODUCT correctly, in order to include the other criteria,
I'll
have to multiply in those other criteria, like this?
=SUMPRODUCT(--(H3:H25-G3:G25=5)*(I3:I25="Excellent"))
That seems to work for me. Is there a way to generalize this to include
all
rows from row 3 onward? (Rows 1 and 2 are labels.)
--David
"T. Valko" wrote:
COUNTIFS can't do that. Try this:
=SUMPRODUCT(--(H1:H25-G1:G25=5))
--
Biff
Microsoft Excel MVP
"David Aukerman" wrote in
message
...
PJ,
What I'd like to do is this:
=COUNTIFS(H:H - G:G, 5)
(There are other criteria I'm checking at the same time, which is why
I'm
using COUNTIFS().) The idea is that I want to check that the
difference
between columns H and G is 5. Does that help?
--David
"PJFry" wrote:
It is unclear to me where the criteria comes in. Is it supposed to be
Count
if the difference is 5 (or something like that)?
--
Regards,
PJ
|