Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

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.. 

Please help me.

ASAP.

Answer
Discuss

Answers

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.

Discuss
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.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login