Using sumproduct with nested arrays

[doug firr]

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?

 

[oldbrewer]

break it into 2 parts by =if(canceldate="-",(*********************),if(canceldate>h21,*****************))

 

[Aladin Akyurek]

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...

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

 

[doug firr]

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]

Test this formula:

=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]

Interesting. So, by saying cancel date is greater than H21 it will already include "-". Why?! Why is "-" greater than h21?

 

[Aladin Akyurek]

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]

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