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?

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