Countifs & OR function

[UHD]

Hi guys,

Please refer to the attached workbook for reference. I need to create a countifs function with an OR component, but can't figure out how. Its probably a sum(countifs(or array function or something like that.

I would like to count:

Id = 1, 2, 3, 4 (the total number of times this ID appears. For this function I reference a cell is another list such as B4=1, B5=2, etc...), and
BW = (1)PY1, and
CH = (2)Not terminated, and
FJ = (2)Not terminated, and
DJ OR GL = (1)Yes

For example: The correct number for ID 1 should be 3 because ID one appears three times with BW=(1)PY1, CH=(2)Not terminated, FJ(2)Not terminated, and although DJ are no's it turned into three yes's in GL. I hope that makes sense

Its the last part, the DJ or GL that is messing me up. This is a partial list for reference purposes only, and not the full list.

[davsth]

Perhaps
=SUMPRODUCT((A2:A30=1)*(B2:B30="(1)PY1")*(C2:C30="(2)Not terminated")*(D2:D30="(2)Not terminated")*(E2:F30="(1)Yes"))

although if both E and F could be yes
=SUMPRODUCT((A2:A30=1)*(B2:B30="(1)PY1")*(C2:C30="(2)Not terminated")*(D2:D30="(2)Not terminated")*(EE230="(1)Yes"))+SUMPRODUCT((A2:A30=1)*(B2:B30="(1)PY1")*(C2:C30="(2)Not terminated")*(D2:D30="(2)Not terminated")*(E2:E30<>"(1)Yes")*(F2:F30="(1)Yes"))

[UHD]

Hi Davsth,

That actually wont work. The columns E and F are not actually right next to each other in the database. The column headings in the attachment (e.g., DJ and GL) refer to the actual column headings in the database. So based on your formula it would be (DJ:GL"(1)Yes") and that would pick up a lot of the corresponding noise in between columns DJ and GL. Sorry for setting up the demo file improperly.

[UHD]

HI guys,

none of those functions worked. But I did finally think of a work around. I will code a cheater column to say true or false if either column DJ or FL ='s yes. Then I can use a regular countifs function.

Thanks!

[davsth]

sumproduct uses a range, so a1:a1000000 would work but a:A would not. Glad you got a solution

you can use countifs for column DJ as yes in one count if + a similar count if with DJ as No and GL as yes, as you only want to count GL if you havent counted that row in DJ


countifs(A:A,1,B:B,"(1)PY1"),C:C,"(2)Not terminated"),D:D,"(2)Not terminated"),DJ:dJ,"(1)Yes") +countifs(a:A,1,B:B,"(1)PY1",C:C,"(2)Not terminated"),D:D="(2)Not terminated",DJ:dJ,"<>(1)Yes",GL:GL, "(1)Yes")