#1
|
|||
|
|||
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
|
||||
|
||||
perhaps:
=SUMPRODUCT((A1:A9000 <=E1)*(((B1:B9000 >=E1)+(B1:B9000 = ""))>0)) ? |
#3
|
|||
|
|||
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
|
||||
|
||||
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
|
|||
|
|||
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 |
#6
|
||||
|
||||
try along the lines of:
Code:
=SUMPRODUCT((C2:C8<=G2)*((C2:C8<>""))*((D2:D8>=G2)+(D2:D8=""))) |
#7
|
|||
|
|||
G1=sumproduct(($c$2:$c$5>=g2)*(($d$2:$d$5<=g2)+($d $2:$d$5="")))
|
#8
|
|||
|
|||
Quote:
Thanks! |
Thread Tools | |
Display Modes | |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Sumproduct | arndts | Excel | 6 | 01-09-2019 01:10 AM |
Countifs and Sumproduct | Algo | Excel | 6 | 11-13-2012 07:44 AM |
sumproduct?? | jer | Excel | 9 | 10-14-2012 10:00 AM |
Sumproduct formula | Portucale | Excel | 2 | 09-12-2012 10:51 AM |
Sumproduct | angie.chang | Excel | 3 | 06-14-2012 10:00 AM |