Array Sum formula using wildcard

Question

Hi Folks

How does one use a wildcard in an array formula?

So this is the formula I'm using.

{=SUM((L2:L207=$B22)*(M2:M207=G$19))}

The values in column L are 6A, 6B, 6C, 5A etc to 3C

So B22 in the example is the reference to one of the above .

However if the value in colum L is 2A, 2B, or 2C, In an other cell I want them counted in one go because the breakdown isn't necessary.

Using =SUM(L2:L207="2*").... is not working neither is =*2?")....

:(

Thanks for any help

Answer 1

In case more than 1 condition, then you may use

=COUNTIFS(L2:L207,"2?",M2:M207,G$19)

Thank you Vjay,

Worked perfectly.

couldn't get the ,"2?" to work with SUMPRODUCT.

Thanks for the advice on using =SUMPRODUCT for array formulas. Saves the ctrl + Shft + Entering.

Which is better =SUMPRODCUT or =COUNTIFS? (apart for the wildcard issue)

Thanks

SUMPRODUCT will not work for "2?". Would need other way to get it.

Regarding which one is better,

Then COUNTIFS is much faster than SUMPRODUCT. So in case you are able to get desired results through countifs, then you should always use countifs only, otherwise SUMPRODUCT.

Answer 2

First thing array formula can be avoided by using this sumproduct formula.

=SUMPRODUCT((L2:L207=$B22)*(M2:M207=G$19))

Regarding using of wild character,

If you want to use only countif then use this formula

=COUNTIF(L2:L207,"2?")

In case more than 1 condition, then you may use

=COUNTIFS(L2:L207,"2?",M2:M207,G$19)

Answer 3

Hi. I'd already seen that post but couldn't apply it to my situation but thanks.

Answer 4

In case more than 1 condition, then you may use

=COUNTIFS(L2:L207,"2?",M2:M207,G$19)

Thank you Vjay,

Worked perfectly.

couldn't get the ,"2?" to work with SUMPRODUCT.

Thanks for the advice on using =SUMPRODUCT for array formulas. Saves the ctrl + Shft + Entering.

Which is better =SUMPRODCUT or =COUNTIFS? (apart for the wildcard issue)

Thanks

Answer 5

Hi JenDingbat,

cross-posted before you have to check the below link hope to find you well   

http://answers.microsoft.com/en-us/office/forum/office\_2003-excel/using-wildcard-in-a-sum-array-in-excel-2003/bf50b3e4-30b8-4b68-b0d9-883797b94c17

Thanks,