ExcelBanter - sumif wildcard criteria

ExcelBanter (https://www.excelbanter.com/)

- Excel Worksheet Functions (https://www.excelbanter.com/excel-worksheet-functions/)

- - sumif wildcard criteria (https://www.excelbanter.com/excel-worksheet-functions/252523-sumif-wildcard-criteria.html)

sumif wildcard criteria

Hi, I cant seem to get the wildcard to work with a simple sumif
I have a range of numbers as below
597910001
587910001
567910001

I wish to sumif on the last five being 10001

I have tried
=SUMIF(Plan!$A1:$A1000,"*10001",Plan!C1:C1000)
=SUMIF(Plan!$A1:$A1000,"5???10001",Plan!C1:C1000)
and all other sorts of variations. I would have thought the wildcard would
work
Thanks Craig

sumif wildcard criteria

Wildcards won't work on numeric values.

Try this...

=SUMPRODUCT(--(RIGHT(Plan!$A1:$A1000,5)="10001"),Plan!C1:C1000)

--
Biff
Microsoft Excel MVP

"craig" wrote in message
...
Hi, I cant seem to get the wildcard to work with a simple sumif
I have a range of numbers as below
597910001
587910001
567910001

I wish to sumif on the last five being 10001

I have tried
=SUMIF(Plan!$A1:$A1000,"*10001",Plan!C1:C1000)
=SUMIF(Plan!$A1:$A1000,"5???10001",Plan!C1:C1000)
and all other sorts of variations. I would have thought the wildcard would
work
Thanks Craig