CODEWITHSUNDEEP
excelexcel-formula
oryan5000
oryan5000

Reputation: 91

Excel: Equation for SUMIFS based on both text and dates

I have several thousand rows of information in one sheet ("Trial Balances Consolidated"), which looks like the image below I would like to sum the value in column D for every row that contains the phrase "Allowance for doubtful accounts", and has one of three date values in Column A. Those 3 date values are 3/1/2019, 3/1/2018, and 3/1/2017. The following formula is resulting in a value of 0. But it should retrieve a value of $4,000 if used in the example below. How would you change this formula?

=SUMIFS('Trial Balances Consolidated'!D:D,'Trial Balances Consolidated'!C:C,"*"&"Allowance for Doubtful Accounts"&"*",'Trial Balances Consolidated'!A:A,AND(OR("3/1/2019","3/1/2018","3/1/2017"))

Example Sheet

Upvotes: 0

Views: 28

Answers (1)

Scott Craner
Scott Craner

Reputation: 152505

Wrap in SUMPRODUCT and do not use AND or OR

=SUMPRODUCT(SUMIFS('Trial Balances Consolidated'!D:D,'Trial Balances Consolidated'!C:C,"*"&"Allowance for Doubtful Accounts"&"*",'Trial Balances Consolidated'!A:A,{"3/1/2019","3/1/2018","3/1/2017"}))

Upvotes: 1

Related Questions