Sumproduct - 2 values in a column

[gberg]

I have the following sumproduct formula that looks at table "JobList_01"

SUMPRODUCT(--(JobList_01_Jan[Type]=Status_POC),--(JobList_01_Jan[Status]=Status_Open),--(JobList_01_Jan[%Comp1]<>""),--(JobList_01_Jan[%Comp2]=1),(JobList_01_Jan[Best JGP]-JobList_01_Jan[Prev JGP]))

It currently looks for a "1" in the "%Comp1" column. I would like to have it look for multiple values in the "%Comp1" column (specifically either a "1" or a blank cell). I've tried various things but usually get an error, I'm hoping someone can help me with this.

Tahnks,

Greg

 

[awoohaw]

@gberg , Can you post some anonymized data in your table format please?

But here is an attempt at editing your formula blindly:

SUMPRODUCT(
--(JobList_01_Jan[Type]=Status_POC),
--(JobList_01_Jan[Status]=Status_Open),
--(JobList_01_Jan[%Comp1]<>""),
--(JobList_01_Jan[%Comp2]=1),
(JobList_01_Jan[Best JGP]-JobList_01_Jan[Prev JGP]))
+
SUMPRODUCT(
--(JobList_01_Jan[Type]=Status_POC),
--(JobList_01_Jan[Status]=Status_Open),
--(JobList_01_Jan[%Comp1]<>""),
--(JobList_01_Jan[%Comp2]=""),
(JobList_01_Jan[Best JGP]-JobList_01_Jan[Prev JGP]))

or

SUMPRODUCT(
--(JobList_01_Jan[Type]=Status_POC),
--(JobList_01_Jan[Status]=Status_Open),
--(JobList_01_Jan[%Comp1]<>""),
(--(JobList_01_Jan[%Comp2]=1) + --(JobList_01_Jan[%Comp2]="")),
(JobList_01_Jan[Best JGP]-JobList_01_Jan[Prev JGP]))

 

[gberg]

awoohaw,
Thanks for the feedback. I tried your second formula and your "blind" attempt worked!!!! Sorry for not providing the table format you requested but since your revised formula worked I don't think it is necessary at this point. Again, thanks for your help!

Greg

 

[awoohaw]

I'm happy you found a solution!
Best Wishes!!