I can't get Excel SUMIF() function to recognize the asterisk wildcard.

Question

Trying to separate sum by year. Standard mm/dd/yyyy date format was used but there are a half dozen blank cells between dates.

=SUMIF($B$9:$B$600,"*2022",$E$9:$E$600)

Results are 0 though there are 2 dozen entries for 2022.

Answer 1

Hi @Robert Spears

Could you please share us with a simple sample?

Based on your description, if you convert dates from Data format to Text format (Such as using =TEXT(A1,"mm/dd/yyyy") to convert), then your formula will work.

Because Excel stores dates as numbers not as text, "*2022" is not recognized as a date.

I suggest you use Sumproduct function. For my sample, the formula should be =SUMPRODUCT((YEAR(A1:A3)=2022)*E1:E3).

---

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

---

Answer 2

Hello,

You can try the following formulas:

1. SUMIFS that meet multiple criteria:

=SUMIFS($E$9:$E$600,$B$9:$B$600,">="&DATE(2022,1,1),$B$9:$B$600,"<"&DATE(2023,1,1))

2. SUMPRODUCT that returns the sum of the products of corresponding ranges:

=SUMPRODUCT(--(YEAR($B$9:$B$600)=2022),$E$9:$E$600)

3. Or add column YEAR and use your formula based on them:

=SUMIF($C$9:$C$600,2022,$E$9:$E$600)

4. SUM + IF:

=SUM(IF(YEAR($B$9:$B$600)=2022,$E$9:$E$600,0))