Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-10-2019, 10:14 AM
ShostyFan ShostyFan is offline sumProduct with OR condition Windows 10 sumProduct with OR condition Office 2007
Novice
sumProduct with OR condition
 
Join Date: Oct 2019
Posts: 4
ShostyFan is on a distinguished road
Default 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?
Reply With Quote
  #2  
Old 10-11-2019, 03:23 AM
p45cal's Avatar
p45cal p45cal is offline sumProduct with OR condition Windows 10 sumProduct with OR condition Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

perhaps:
=SUMPRODUCT((A1:A9000 <=E1)*(((B1:B9000 >=E1)+(B1:B9000 = ""))>0))
?
Reply With Quote
  #3  
Old 10-14-2019, 08:07 AM
ShostyFan ShostyFan is offline sumProduct with OR condition Windows 10 sumProduct with OR condition Office 2007
Novice
sumProduct with OR condition
 
Join Date: Oct 2019
Posts: 4
ShostyFan is on a distinguished road
Default

Quote:
Originally Posted by p45cal View Post
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.
Reply With Quote
  #4  
Old 10-14-2019, 08:31 AM
p45cal's Avatar
p45cal p45cal is offline sumProduct with OR condition Windows 10 sumProduct with OR condition Office 2016
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

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.
Reply With Quote
  #5  
Old 10-16-2019, 09:27 AM
ShostyFan ShostyFan is offline sumProduct with OR condition Windows 10 sumProduct with OR condition Office 2007
Novice
sumProduct with OR condition
 
Join Date: Oct 2019
Posts: 4
ShostyFan is on a distinguished road
Default

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
File Type: xlsx DateCountHelp.xlsx (250.3 KB, 8 views)
Reply With Quote
  #6  
Old 10-16-2019, 10:57 AM
p45cal's Avatar
p45cal p45cal is offline sumProduct with OR condition Windows 10 sumProduct with OR condition Office 2019
Expert
 
Join Date: Apr 2014
Posts: 863
p45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant futurep45cal has a brilliant future
Default

try along the lines of:
Code:
=SUMPRODUCT((C2:C8<=G2)*((C2:C8<>""))*((D2:D8>=G2)+(D2:D8="")))
Reply With Quote
  #7  
Old 10-16-2019, 11:23 AM
CARACALLA CARACALLA is offline sumProduct with OR condition Windows 10 sumProduct with OR condition Office 2019
Novice
 
Join Date: Oct 2019
Posts: 1
CARACALLA is on a distinguished road
Default

G1=sumproduct(($c$2:$c$5>=g2)*(($d$2:$d$5<=g2)+($d $2:$d$5="")))
Reply With Quote
  #8  
Old 10-16-2019, 02:19 PM
ShostyFan ShostyFan is offline sumProduct with OR condition Windows 10 sumProduct with OR condition Office 2007
Novice
sumProduct with OR condition
 
Join Date: Oct 2019
Posts: 4
ShostyFan is on a distinguished road
Default

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

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
Sumproduct arndts Excel 6 01-09-2019 01:10 AM
sumProduct with OR condition Countifs and Sumproduct Algo Excel 6 11-13-2012 07:44 AM
sumproduct?? jer Excel 9 10-14-2012 10:00 AM
sumProduct with OR condition Sumproduct formula Portucale Excel 2 09-12-2012 10:51 AM
sumProduct with OR condition Sumproduct angie.chang Excel 3 06-14-2012 10:00 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 12:26 PM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft