Excel 2007: complex COUNTIFS()
 

I am trying to do a COUNTIFS() function in which I need to compare the
difference between two numeric cells in the same row. In my spreadsheet,
column G and column H contain year values (stored as integers). One of the
criteria in my COUNTIFS() needs to check the difference between adjacent
cells in these columns, for example, if H4-G4 = 5 or if H100-G100 = 10. I
know the "easy" way would be to create a new column containing the
differences, but I need to avoid that shortcut. Any ideas?

Excel 2007: complex COUNTIFS()
 

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
Please rate this post using the vote buttons if it was helpful.



"David Aukerman" wrote:

I am trying to do a COUNTIFS() function in which I need to compare the
difference between two numeric cells in the same row. In my spreadsheet,
column G and column H contain year values (stored as integers). One of the
criteria in my COUNTIFS() needs to check the difference between adjacent
cells in these columns, for example, if H4-G4 = 5 or if H100-G100 = 10. I
know the "easy" way would be to create a new column containing the
differences, but I need to avoid that shortcut. Any ideas?

Excel 2007: complex COUNTIFS()
 

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

Excel 2007: complex COUNTIFS()
 

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

Excel 2007: complex COUNTIFS()
 

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

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

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

Excel 2007: complex COUNTIFS()
 

is there an advantage to using multiple arrays like you described?
(i.e. is it computationally faster?)

Yes, it's slightly faster (in most cases) to calculate.

See:

http://xldynamic.com/source/xld.SUMPRODUCT.html

Any chance that I could reference a
cell containing the last row to check?
I think I might be setting my hopes too
high this time.

No problem! Use dynamic ranges. Assumes no empty cells *within* the ranges.

In Excel 2007...
Formulas tabDefined NamesName Manager
Click the New button
Name: Range1
Refers to:
=Sheet1!$H$3:INDEX(Sheet1!$H:$H,COUNT(Sheet1!$H:$H )+2)

OK

Repeat this and create 2 more named ranges:

Range2
Refers to:
=Sheet1!$G$3:INDEX(Sheet1!$G:$G,COUNT(Sheet1!$H:$H )+2)

Range3
Refers to:
=Sheet1!$I$3:INDEX(Sheet1!$I:$I,COUNT(Sheet1!$H:$H )+2)

Use your actual sheet name in the above formulas!

Then the SUMPRODUCT formula becomes:

=SUMPRODUCT(--(Range1-Range2=5),--(Range3="excellent"))

--
Biff
Microsoft Excel MVP


"David Aukerman" wrote in message
...
=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

Excel 2007: complex COUNTIFS()
 

See:

http://xldynamic.com/source/xld.SUMPRODUCT.html

Very interesting reading, thanks!

No problem! Use dynamic ranges.

Fantastic! I'll give it a shot when I've caught up on my sleep. :)

Many thanks,
--David

Can the sumproduct be calculated on more than one column at a time?
For example,

=SUMPRODUCT(--(D$2:D$49=""),--(E$2:E$49="TEST"))
works great, but

=SUMPRODUCT(--(B$2:D$49=""),--(E$2:E$49="TEST"))
returns #VALUE