if then statement w/ sumproduct

C.S.

Board Regular
Joined
Nov 20, 2008
Messages
89
I can't get this formula to work. I suspect I'm missing something relatively simple.

IF(H6=".Last Three Months.",SUMPRODUCT(--('Data Entry'!F4:F503=>'formulas'!H1),--('Data Entry'!F4:F503=<'formulas'!H2),--('Data Entry'!K4:K503,"Yes"),SUMPRODUCT(--('Data Entry'!H4:H503=H6),--('Data Entry'!K4:K503="YES")))

The way i split it up by color is how I want it to go... IF(logical test,value if true, value if false. However, its just combining the green and black as the value if true. I've tried several different things... extra parenths, commas, etc. Nothing seems to work.

Thanks for the help.
Clint
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
One thing I notice right away is it needs to be >= and not => and the same for your less than signs. <= not =<.

Oh sorry I see that is part of the sheet name and not actually being used let me look again.

Hope that helps.
 
Upvote 0
Try

=IF(H6=".Last Three Months.",SUMPRODUCT(--('Data Entry'!F4:F503>=formulas!H1),--('Data Entry'!F4:F503<=formulas!H2),--('Data Entry'!K4:K503="Yes")),SUMPRODUCT(--('Data Entry'!H4:H503=H6),--('Data Entry'!K4:K503="YES")))

Had a missing parenthesis

And, these symbols were backwards

=> needs to be >=
 
Upvote 0
Ok I realized again it was not part of th sheet name. :)

You had a parenthesis in the worng place too:

=IF(H6=".Last Three Months.",SUMPRODUCT(--('Data Entry'!F4:F503=>'formulas'!H1),--('Data Entry'!F4:F503=<'formulas'!H2),--('Data Entry'!K4:K503,"Yes")),SUMPRODUCT(--('Data Entry'!H4:H503=H6),--('Data Entry'!K4:K503="YES")))

I added the bold red one.
 
Upvote 0
schielrn, you were right, I was missing one paren as you pointed out. Thanks, but now the formula won't work even though its chopped up right. Now it just says, "the formula you typed contains and error" but doesn't specify what's wrong, it just highlights the entire formula. Do you think I have too many arguments?

IF(H6=".Last Three Months.",SUMPRODUCT(--('Data Entry'!F4:F503>=G2),--('Data Entry'!F4:F503<=G1),--('Data Entry'!K4:K503,"Yes")),SUMPRODUCT(--('Data Entry'!H4:H503=H6),--('Data Entry'!K4:K503="YES")))
 
Upvote 0
playing around with it, it looks like it has something to do with the dates i'm referencing. G2 & G1 are dates determined from a formula (=today()) and then (=date(Year(G1),Month(........)). It won't work even if I just enter the dates without using a formula.

IF(H6=".Last Three Months.",SUMPRODUCT(--('Data Entry'!F4:F503>=G2),--('Data Entry'!F4:F503<=G1),--('Data Entry'!K4:K503,"Yes")),SUMPRODUCT(--('Data Entry'!H4:H503=H6),--('Data Entry'!K4:K503="YES")))
 
Upvote 0
Jonmo's formula works, but it looks like you have a comma in place of a = sign in yours:

IF(H6=".Last Three Months.",SUMPRODUCT(--('Data Entry'!F4:F503>=G2),--('Data Entry'!F4:F503<=G1),--('Data Entry'!K4:K503,"Yes")),SUMPRODUCT(--('Data Entry'!H4:H503=H6),--('Data Entry'!K4:K503="YES")))
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,325
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top