Using sumproduct with nested arrays

doug firr

Board Regular
Joined
Mar 28, 2011
Messages
140
Here's the formula I'm trying to use:

=SUMPRODUCT((subscriptionType="premium")*(hasPaymentInfo="Yes")*(payDate>='db analysis'!H$20)*(payDate<='db analysis'!H$21)*(OR(cancelDate="-",cancelDate>H21)))

It's not working, I know because I can check by appllying auto filter on the data sheet and counting the rows. It's the nested OR statement, I'm sure of it.

I want to say sumproduct where named range subscription type is premium, hasPaymentInfo is yes, payDate is within a range (7 days) and where cancelDate is either "-" or after the end of the week being analysed (week end date is in H21)

But instead it looks like sumproduct.

Can anyone see a flaw in the logic here?
 
Last edited:

Some videos you may like

Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

oldbrewer

Board Regular
Joined
Apr 11, 2010
Messages
11,003
break it into 2 parts by =if(canceldate="-",(*********************),if(canceldate>h21,*****************))
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Here's the formula I'm trying to use:

=SUMPRODUCT((subscriptionType="premium")*(hasPaymentInfo="Yes")*(payDate>='db analysis'!H$20)*(payDate<='db analysis'!H$21)*(OR(cancelDate="-",cancelDate>H21)))

It's not working, I know because I can check by appllying auto filter on the data sheet and counting the rows. It's the nested OR statement, I'm sure of it.

I want to say sumproduct where named range subscription type is premium, hasPaymentInfo is yes, payDate is within a range (7 days) and where cancelDate is either "-" or after the end of the week being analysed (week end date is in H21)

But instead it looks like sumproduct.

Can anyone see a flaw in the logic here?

Try...
Rich (BB code):
=SUMPRODUCT(
  (subscriptionType="premium")*
  (hasPaymentInfo="Yes")*
  (payDate>='db  analysis'!H$20)*
  (payDate<='db  analysis'!H$21)*
  ((cancelDate="-")+(cancelDate>H$21)>0))
 

doug firr

Board Regular
Joined
Mar 28, 2011
Messages
140
Thanks (again) Aladin this is working now. But why? I did try that, except I never included the )>0) part - what's that for?
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699

ADVERTISEMENT

Test this formula:

Code:
=SUMPRODUCT((subscriptionType="premium")*(hasPaymentInfo="Yes")*(payDate>='db analysis'!H$20)*(payDate<='db analysis'!H$21)*([COLOR="#0000FF"]cancelDate>H21[/COLOR]))

PS: "-" is > H21.

Markmzz
 

doug firr

Board Regular
Joined
Mar 28, 2011
Messages
140
Interesting. So, by saying cancel date is greater than H21 it will already include "-". Why?! Why is "-" greater than h21?
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
Thanks (again) Aladin this is working now. But why? I did try that, except I never included the )>0) part - what's that for?

A text value like "-" is always > than a number. When "-" holds for a cell along with other conditions, this same cell will score as > H21 that houses a number. The > 0 testing involving the + structure avoids double counting.
 

markmzz

MrExcel MVP
Joined
May 7, 2011
Messages
3,699
Interesting. So, by saying cancel date is greater than H21 it will already include "-". Why?! Why is "-" greater than h21?

In Excel, any caracter (like txt - " ", "-", "a" and "1") is great than any number (by the way, a date in Excel is a number).

Do some tests.

Markmzz
 

Watch MrExcel Video

Forum statistics

Threads
1,118,418
Messages
5,571,990
Members
412,430
Latest member
Huuktkt
Top