Forum Discussion
Error Message Countif
Hi Haytham,
Array with COUNTIF generates OR condition, and here we need AND condition. I'd use
=SUMPRODUCT(--(A2:A40<>"TBA"),--(B2:B40="Fitter"),--(E2:E40>=43101))
where 43101 = Jan 01, 2018 (to avoid specific of regional settings)
Hi Sergei,
I have understood from her question that she want to count each criterion seperately using OR logic.
Anyway, she can choose the solution that agree what she meant.
By the way, thanks about your point to use the serial number of date in stead of the date format to avoid some different regional settings.
Hi Haytham,
Afraid even with OR logic array formula gives wrong result since it's used on multidimensional cells array.
Assume required logic is
(<>"TBA" in A column) OR ('="Fitter" in B column) OR ('>43101 in E column)Okay, skip for a while C and D column, let say all data is from A to C. Totally we have 39 rows, first condition ("<>TBA") is checked on all three (A,B,C) columns, etc. Result will be something like this
if we have 4 not TBA in A, 5 Fitter in B and 7 proper dates in C. Answer for
=SUM(COUNTIF(A2:C40,{">=43101","<>TBA","Fitter"}))will be 125.
Simple COUNIF(on A)+COUNTIF(on B)+COUNTIF(on C) gives
But we have only 39 records in our array.
For the AND logic we may use
=COUNTIFS(A2:A40,"<>TBA",B2:B40,"Fitter",C2:C40,">=43101")
which is equivalent of SUMPRODUCT in previous message.
For the OR logic i'd simply invert the conditions, when
=COUNTIFS(A2:A40, "TBA", B2:B40, "<>Fitter",C2:C40,"<=43101")
gives number of records which don't meet our conditions, and something like
=COUNTA(A2:A40)-COUNTIFS(A2:A40, "TBA", B2:B40, "<>Fitter",C2:C40,"<=43101")
gives the final result for OR condition.