Help with COUNTIF / OR formula and SUMIF / OR formula

[willow1985]

I need help with a formula or possibly another way to figuring out this problem:

I want to count everything in 'WO Report'!$Y:$Y that states: TRUE

Only IF

'WO Report'!B:B Matches List!B2

However only if there is no match between 'WO Report'!B:B and List!B2

I want to count everything in 'WO Report'!$Y:$Y that states: TRUE

IF

'WO Report'!Z:Z, Matches List!B2

So basically toggling between: =COUNTIFS('WO Report'!$Y:$Y,TRUE,'WO Report'!B:B,List!B2 OR 'WO Report'!$Y:$Y,TRUE,'WO Report'!Z:Z,List!B2)

Is there a way to do this?

I will also require the same with a SUMIF but it will reference different columns.


Note: I am using a combo box that has a link to B2, however when the combo box is selected to ALL, I want the formula to count everything but there is no match to "ALL", so what I did was added a column (WO Report'!Z:Z) that just has the word "ALL" all the way down and would have the formula count that if Column Y:Y is TRUE. I have no idea if this is even possible but hope someone can help.

Thank you very much!

Carla

 

[willow1985]

This formula seems to be working:

COUNTIFS('WO Report'!$Y:$Y,TRUE,'WO Report'!B:B,List!B2)+(COUNTIFS('WO Report'!$Y:$Y,TRUE,'WO Report'!Z:Z,List!B2))

Let me know if I am on the right track?

Thank you everyone!

 

[Eric W]

Definitely on the right track. However, one question: will the List!B2 value be in column B OR column Z? Is there a chance it could be in both? If so, you'll end up double counting some rows. Your second COUNTIFS has some unneeded parentheses too, but they don't hurt anything.

 

[willow1985]

Column B and Column Z will never have the same values.

Column B is 255 customer names and Column Z is the word "ALL" repeated for all data.

So if the formula doesn't find a match for column B it will for Z and vice versa but never for both.

Always appreciate info on how to make a formula more efficient though, I still have a lot to learn

Thank you!