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

Robert Spears 0 Reputation points
2024-09-21T22:53:08.4333333+00:00

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.

Microsoft 365
Microsoft 365
Formerly Office 365, is a line of subscription services offered by Microsoft which adds to and includes the Microsoft Office product line.
4,824 questions
Excel
Excel
A family of Microsoft spreadsheet software with tools for analyzing, charting, and communicating data.
1,875 questions
Microsoft Managed Desktop
Microsoft Managed Desktop
A cloud-based service that brings together Microsoft 365 Enterprise and adds these features: User device deployment; IT service management and operations; and Security monitoring and response.
56 questions
Microsoft Copilot for Microsoft 365 Development
Microsoft Copilot for Microsoft 365 Development
Microsoft Copilot for Microsoft 365: Microsoft 365 Copilot refers collectively to Copilot experiences within Microsoft 365 applications.Development: The process of researching, productizing, and refining new or existing technologies.
136 questions

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Jiajing Hua-MFST 9,760 Reputation points Microsoft Vendor
    2024-09-24T02:36:42.3+00:00

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

    User's image

    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.

  2. AnatoliiNosulich 0 Reputation points
    2024-09-24T13:41:46.5233333+00:00

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

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

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

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

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

    1. SUM + IF:

    =SUM(IF(YEAR($B$9:$B$600)=2022,$E$9:$E$600,0))
    User's image

    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.