Countif function with Multiple logic in one Criteria

Question

Can we use Multiple logic in single criteria in Countif function in VBA?

Example: Col A contains similar names like Joe and Joseph I need the count of these using Countif function, how can I achieve this?

Thanks in advance...

mdialogo · Accepted Answer · 2017-09-21 23:28:45Z

2

Try:

=COUNTIF(A1:A3,"Joe") + COUNTIF(A1:A3,"Joseph")

answered Sep 21, 2017 at 23:28

mdialogo

4738 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

In real data I have four options to count so I need to use four Countif function, is there any better way.

@Ganesh have you tried the COUNTIFS()? Note that this function is only available Excel 2007 or later

L42 · Accepted Answer · 2017-09-22 00:42:08Z

1

You may try wildcards if your criteria follows specific format like:

=COUNTIF(A1:A10,"Jo*")

Above will count all entries beginning Jo.

Another way is to use array constant and incorporate SUMPRODUCT function.

=SUMPRODUCT(COUNTIF(A1:A10,{"Joseph","Joe"}))

Above counts Joseph and Joe. You can also use Range reference in place of array constants.

=SUMPRODUCT(COUNTIF(A1:A10,B1:B2))

Where B1:B2 contains your criteria.

answered Sep 22, 2017 at 0:42

L42

19.8k11 gold badges47 silver badges70 bronze badges

The above functions are working in excel, but can we use the same formula in VBA/Macro?

@Ganesh Yes. Indeed you can use it in VBA.