Microsoft Office Forums sumProduct with OR condition
 User Name Remember Me? Password
 Register FAQ Search Today's Posts Mark Forums Read

 Thread Tools Display Modes
#1
10-10-2019, 10:14 AM
 ShostyFan Windows 10 Office 2007 Novice Join Date: Oct 2019 Posts: 4
sumProduct with OR condition

Here is my formula:
=SUMPRODUCT((A1:C90000 <=E1)*(B1:B90000 >=E1)*(B1:B90000 <> ""))

It does not return the correct results, what I want it do is this:

=SUMPRODUCT((A1:A90000 <=E1)*(B1:B90000 >=E1 OR isNULL))

How do I make excel like what I am trying to tell it?
#2
10-11-2019, 03:23 AM
 p45cal Windows 10 Office 2016 Expert Join Date: Apr 2014 Posts: 291

perhaps:
=SUMPRODUCT((A1:A9000 <=E1)*(((B1:B9000 >=E1)+(B1:B9000 = ""))>0))
?
#3
10-14-2019, 08:07 AM
 ShostyFan Windows 10 Office 2007 Novice Join Date: Oct 2019 Posts: 4

Quote:
 Originally Posted by p45cal perhaps: =SUMPRODUCT((A1:A9000 <=E1)*(((B1:B9000 >=E1)+(B1:B9000 = ""))>0)) ?
Thanks for the idea, but sadly it does not work either, adding the greater then 0 to the end gives me a result of 0 when there is desired data to be counted.
#4
10-14-2019, 08:31 AM
 p45cal Windows 10 Office 2016 Expert Join Date: Apr 2014 Posts: 291

I prefixed my answer with the word 'perhaps' because I have to guess what you want, and I've probably guessed wrongly.
Your first formula includes looking at column C but your second formula doesn't.

I haven't just added 'the greater than 0 at the end', there is a crucial change of operator from multiply to add and some extra nested parentheses to supply the necessary OR.

I'm likely to be able to give you a definitive answer if you were to attach a workbook with some data and expected results from that data.

Some narrative would also be helpful because, so far, we have to rely only on your formulae to gauge what you want and with all respect (a) you may not have translated what you want correctly (b) which is supported by your formulae not working.
#5
10-16-2019, 09:27 AM
 ShostyFan Windows 10 Office 2007 Novice Join Date: Oct 2019 Posts: 4

Thank you so much for looking at this, attached is a simple sheet with data.
My formula is in G1
I want the user to be able to enter a single date, this is cell G2
The formula checks all dates in column C and considers all dates less then the user entered date as true
The formula also checks column D and considers all dates greater then the user date as true
The formula also checks if there is a blank cell in column D and would return that as true

In the sample data the correct answer should be 2
Attached Files
 DateCountHelp.xlsx (250.3 KB, 3 views)
#6
10-16-2019, 10:57 AM
 p45cal Windows 10 Office 2019 Expert Join Date: Apr 2014 Posts: 291

try along the lines of:
Code:
`=SUMPRODUCT((C2:C8<=G2)*((C2:C8<>""))*((D2:D8>=G2)+(D2:D8="")))`
#7
10-16-2019, 11:23 AM
 CARACALLA Windows 10 Office 2019 Novice Join Date: Oct 2019 Posts: 1

G1=sumproduct((\$c\$2:\$c\$5>=g2)*((\$d\$2:\$d\$5<=g2)+(\$d \$2:\$d\$5="")))
#8
10-16-2019, 02:19 PM
 ShostyFan Windows 10 Office 2007 Novice Join Date: Oct 2019 Posts: 4

Quote:
 Originally Posted by p45cal try along the lines of: Code: `=SUMPRODUCT((C2:C8<=G2)*((C2:C8<>""))*((D2:D8>=G2)+(D2:D8="")))`
works perfectly.
Thanks!

 Thread Tools Display Modes Linear Mode

 Similar Threads Thread Thread Starter Forum Replies Last Post arndts Excel 6 01-09-2019 01:10 AM Algo Excel 6 11-13-2012 07:44 AM jer Excel 9 10-14-2012 10:00 AM Portucale Excel 2 09-12-2012 10:51 AM angie.chang Excel 3 06-14-2012 10:00 AM

All times are GMT -7. The time now is 09:20 AM.

 -- Default Style -- Lightweight -- New Mobile Contact Us - Privacy Statement - Top