Forum Discussion
Error Message Countif
In Excel 2013: Im trying to count the names in column A (not including TBA) =COUNTIF A2:A40, "<TBA")
plus the number of Fitters in column B =COUNTIF A2:A40, "<TBA", B2:B40,"=Fitter",
plus year in column E, (only if greater than 2017) however I keep getting an error message?? Please help
=COUNTIF A2:A40, "<TBA", B2:B40,"=Fitter", E2:E40, ">2017")
4 Replies
- Haytham AmairahSilver Contributor
Dear,
Try this formula:
=SUM(COUNTIF(A2:C40,{">12/31/2017","<>TBA","Fitter"})) But you have to move the date column from column E to C, and select all the three columns If you can't do that try this: =SUM(COUNTIF(A2:B40,{"<>TBA","Fitter"}))+COUNTIF(E2:E40,">12/31/2017")
- SergeiBaklanDiamond Contributor
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)
- Haytham AmairahSilver Contributor
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.