Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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". |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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)) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
multiple variables in sumproduct or if/then formulas | Excel Worksheet Functions | |||
Too Many Variables | Excel Worksheet Functions | |||
Variables in VBA | Excel Discussion (Misc queries) | |||
Too many variables? | Excel Worksheet Functions | |||
SUM IF and two variables | Excel Worksheet Functions |