sumproduct with 3 variables
 

Trying to count the # times that 3.25 shows up in rows that meet multiple
criteria.

I've got this formula working with 2 variables:
=SUMPRODUCT(--($B$9:$B$16=3.25),--($D$9:$D$16="1001"))

But I want to add another and that seems to foul it up (returns 0, instead
of the correct count #). My last two criteria are to limit it to within 30
days of a date. Any ideas what I have wrong here?
=SUMPRODUCT(--($B$9:$B$16=3.25),--($D$9:$D$16="1001"),(--($A5-($A$9:$A$16))<30))

sumproduct with 3 variables
 

Hi,

Try this

=(SUMPRODUCT(--($B$9:$B$16=3.25),--($D$9:$D$16=1001),--($A$9:$A$16=A5)))-(SUMPRODUCT(--($B$9:$B$16=3.25),--($D$9:$D$16=1001),--($A$9:$A$16=A5+30)))

If 1001 is really text put the quotes back, this treats 1001 as a number.

Mike


"Laury" wrote:

Trying to count the # times that 3.25 shows up in rows that meet multiple
criteria.

I've got this formula working with 2 variables:
=SUMPRODUCT(--($B$9:$B$16=3.25),--($D$9:$D$16="1001"))

But I want to add another and that seems to foul it up (returns 0, instead
of the correct count #). My last two criteria are to limit it to within 30
days of a date. Any ideas what I have wrong here?
=SUMPRODUCT(--($B$9:$B$16=3.25),--($D$9:$D$16="1001"),(--($A5-($A$9:$A$16))<30))

sumproduct with 3 variables
 

I got results with this:
=SUMPRODUCT(--($B$9:$B$16=3.25),--($D$9:$D$16="1001"),--($A$9:$A
$16<=A5+30))

but they may not be your results. This works if your criteria is "less
than or equal to 30 days after the date in A5".

sumproduct with 3 variables
 

Thanks, but unfortunatley I tried it and still get a 0 result. I tried
putting my date criteria before the 1001 (that is text) and for some reason,
it worked. I really don't understand why my 1st one or yours didn't work.
Thanks anyway - that was a creative way to think of it.

=SUMPRODUCT(--($B$9:$B$16=H5),--(($A4-($A$9:$A$16))<31),--(($A5-($A$9:$A$16))-31),--($D$9:$D$16="1001"))

"Mike H" wrote:

Hi,

Try this

=(SUMPRODUCT(--($B$9:$B$16=3.25),--($D$9:$D$16=1001),--($A$9:$A$16=A5)))-(SUMPRODUCT(--($B$9:$B$16=3.25),--($D$9:$D$16=1001),--($A$9:$A$16=A5+30)))

If 1001 is really text put the quotes back, this treats 1001 as a number.

Mike


"Laury" wrote:

Trying to count the # times that 3.25 shows up in rows that meet multiple
criteria.

I've got this formula working with 2 variables:
=SUMPRODUCT(--($B$9:$B$16=3.25),--($D$9:$D$16="1001"))

But I want to add another and that seems to foul it up (returns 0, instead
of the correct count #). My last two criteria are to limit it to within 30
days of a date. Any ideas what I have wrong here?
=SUMPRODUCT(--($B$9:$B$16=3.25),--($D$9:$D$16="1001"),(--($A5-($A$9:$A$16))<30))