SUMIFS Formula Problem

Hi all,

Hoping for some help here. I'm trying to write a SUMIFS formula that works on an Excel Table and adds a calculated field based on given criteria including a date field. The current formula is below:

=SUMIFS(Table1[Daily Charge],Table1[Supply No.],$A4,Table1[Line Type],$B4,Table1[Start Read Date],"<="&C$1,Table1[End Read Date],">="&C$1)

This isn't giving me the required result and to be honest I can't tell what it is actually giving me... If I change the formula to an array formula as follows:

{=SUM(Table1[Daily Charge]*(Table1[Supply No.]=$A5)*(Table1[Line Type]=$B5)*(Table1[Start Read Date]<=C$1)*(Table1[End Read Date]>=C$1))}

Then it works perfectly. The only problem is that I need a lot of these formulae on my sheet and the array formula takes up more memory and more time to compute. Help with the SUMIFS would therefore be greatly appreciated!

Thanks
Alex

Sorry, do you mean entering as:

SUMIFS(Table1[Daily Charge],Table1[Supply No.],$A5,Table1[Line Type],$B5,Table1[Start Read Date],<=C$1,Table1[End Read Date],>=C$1)

If so then I get an error message. I think Excel expects the condition to be entered as 'text' for example if I wanted >0 then I'd have to enter ">0"

Best guess is that the values in Table1[Daily Charge] are not real numbers.
They're 'Numbers Stored As Text'

try copying a blank cell
Highlight Table1[Daily Charge]
Right Click - Paste Special - Values - Add - OK

Nope, they're definitely real numbers. The problem seems to be with the [Supply No.].

I just tried desensitising the data and reducing the volume to post a smaller workbook and thought I'd reduce the data to just the supply number I was after and suddenly the right numbers appeared. So it seems that it can tell everything else, just not the supply number.

The supply number is No. Stored as Text so 008450091200010165884 as an example. It is stored as text in both the data source and the lookup criteria

I've just discovered that looking for 008450091200010165884 has picked up 008450091200010165893 as well

Is this that the text/number string is too long?

Well it's only going to be guesses at this point.

Go ahead and post a sample book.

Sample workbook attached

I don't understand what the problem is?

Both formulas produce the same result.

In the file you posted, you added the round function to the array version
=ROUND(SUM(Table1[Daily Charge]*(Table1[Supply No.]=$A2)*(Table1[Line Type]=$B2)*(Table1[Start Read Date]<=C$1)*(Table1[End Read Date]>=C$1)),2)

This produces the exact same resut.
=ROUND(SUMIFS(Table1[Daily Charge],Table1[Supply No.],$A2,Table1[Line Type],$B2,Table1[Start Read Date],"<="&C$1,Table1[End Read Date],">="&C$1),2)

I did just notice that in your original post, the Sumifs formula referenced A4 and B4, but the array formula referenced A5 and B5

That would explain the different results...

Not for all lines it doesn't - or at least it doesn't on my computer!

It works for the Availability charge but doesn't work for Climate Change Levy or any of the others

Sorry, they were just sample formulas to give the principle of what I was trying to do...

Note that Availability charge only exists for 008450091200010165884 whereas all other items exist on both 008450091200010165884 and 008450091200010165893

I see now.
I mistakenly assumed you had the array formula in all 5 cells in the book you posted.
But you only had it in C2, C3 to C6 had the sumifs.

Then my testing in another set of cells using just the sumifs revealed the same results.


You seem to be right. The sumifs I believe is evaluating only the first 16 digits.
That is actually not surprising, I thought 16 significant digits was the limit to excel's calculations.
Not sure why the array version evaluates the whole thing.

Me either... And this is stored as text so I would have thought it would have no limit. The SUMIFS must be converting the data to numeric for some reason.

There is a workaround I've found as there is another identifier that is tied to the supply number but it is only 7 digits long so it works perfectly.

Thanks for your help on this and perhaps this string will help another confused person in days to come

Here's an alternate approach, still without entering with CTRL + SHIFT + ENTER

=ROUND(SUMPRODUCT(Table1[Daily Charge],--(Table1[Supply No.]=$A2),--(Table1[Line Type]=$B2),--(Table1[Start Read Date]<=C$1),--(Table1[End Read Date]>=C$1)),2)

Thanks - basically using SUMPRODUCT in replacement of the array. Good to see that formula in action as I've never really committed the time to getting it to work in the past. Does it evaluate quicker than an array formula?

I honestly couldn't say.
It's technically still an array formula, you just don't have to enter with CSE.

I think it would be faster, but probably not much.