# COUNTIFS of multiple of non numeric with IF function

0

I have a database table (simple data table). I need to count non numeric (text) in the table. Below, is a formula I'm working for.

``````A formula in Cell F2,
=SUM(COUNTIFS(A:A,{"AM","PM"},B:B,{"X";"Y";"Z"},C:C,"="&D1)) ``````

*D1=date in MM/DD/YYYY

My questions is,

1) How to write the next array formula (just in case I need to add 1 more multiple criteria)  in the formula above as I mention? 1st array I use "," then 2nd array I use ";"  so Excel can read my formula.. HOW?

2)I want to add IF function in the formula above, and I write it as mention below,

A formula function in Cell F2,

``=IF(SUM(COUNTIFS(A:A,{"AM","PM"},B:B,{"X";"Y";"Z"},C:C,"="&D1))) ``

But, the problem is, Excel can't ready my formula..I didn't get the result I want..

ASAP.

0

Make both arrays use semi-colons and then add another one following the same format.

``=SUM(COUNTIFS(A:A,{"AM";"PM"},B:B,{"X";"Y";"Z"},C:C,"="&D1))``

You didn't mention what the IF statement should check for so I can't help with that.

Also, if you are in Excel earlier than version 2019 or 365 you should input the array using Ctrl + Shift + Enter.

0

Frankly, I don't understand what you are trying to make your formulas do but if you "need to count non numeric (text) in the table", as you say, this approach is much simpler.

``=COUNTA(A:A)-COUNT(A:A)``

COUNTA() counts all entries in the column that aren't blank, including formulas that might return a blank. COUNT() counts all cells with numbers in them, including dates. The difference between the two should be the count of cells with non-numeric values.

If you are interested in pursuing this path please consider adding a neutered copy of your workbook to your question. You can do that in Edit mode.