SUMIFS function has suddenly stopped working in one cell

[tomplum]

Please help if you can...

I setup a spreadsheet for reports last year that contains quite a few SUMIFS calculations.

I have several tabs on the spreadsheet that each have these calculations and the majority all work fine.

However, in every tab I have one of these SUMIFS calculations in cell L27. This calculation is now returning no results at all.

The formula I'm using is =SUMIFS(G:G,D:D,"Mural")

Any thoughts on how to fix will be greatly appreciated.

 

[Aladin Akyurek]

Does the following work?...

=SUMIFS(G:G,D:D,"*Mural*")

I believe Aladin is onto the right idea with using the wildcard character "*Mural*" in the formula, but the formula still needs to be
=SUMIF(D:D,"*Mural*",G:G) (with the criteria in the center of the column designations). I've tested this and it works perfectly, even if the word "mural" is all lowercase. I'm guessing your sheet has a leading or trailing space where "mural" is listed that the wildcard character will take care of.

Hi

Structure of your sumif formula is wrong

Sumif(Range, Criteria, SumRange)

always use "=Text" while not referring a cell

=SUMIFS(G:G,D:D,"*Mural*")

=SUMIF(D:D,"*Mural*",G:G)

are exactly equivalent in behavior. The latter can be considered obsolete (also COUNTIF and AVERAGEIF).

 

[KenDue]

Aladin,

After seeing your post directly above, I re-tried the formula you gave, and see that it does indeed work the same. I was not aware that the SUMIFS formula had outdated SUMIF as you described... I thought SUMIFS was only used where more than one criteria was desired. Thanks for the education!

 

[Aladin Akyurek]

Aladin,

After seeing your post directly above, I re-tried the formula you gave, and see that it does indeed work the same. I was not aware that the SUMIFS formula had outdated SUMIF as you described... I thought SUMIFS was only used where more than one criteria was desired. Thanks for the education!

Qualification "obsolete" is rather a personal opinion:

COUNT, COUNTA: 0 arg; COUNTIF:1 arg; COUNTIFS: >=1 arg(s)
SUM: 0 arg; SUMIF: 1 arg; SUMIFS: >= 1 arg(s)
AVERAGE: 0 arg; AVERAGEIF: 1 arg; AVERAGEIFS: >=1 arg(s)
MAX: 0 arg; no MAXIF: 1 arg; MAXIFS: >= 1 arg(s)
MIN: 0 arg; no MINIF: 1 arg; MINIFS: >= 1 arg(s)

>= 1 arg(s) motivates my reason regarding just 1 arg versions.