Way to identify the values being summed in a SUM function

[JulianS96]

Hi forum,
Just a quick one,
Wondering is there is a way to find out which values are being summed together in a SUM or SUMIF function.
i.e. =SUM(A1:A4) = 13 (if A1 = 2, A2 = 4, A3 = 6, A4 = 1)
Is there a way to find out which values excel used to get the answer 13?
Cheers,
Julian

[KOKOSEK]

Is there a way to find out which values excel used to get the answer 13?

Yes, values from range A1:A4.




Seriously, I don't understand what do you expect.....

[MrShorty]

The SUM() function will include every numeric value in the sum. When using the SUM() function, put something like =ISNUMBER(A1) into B1 and copy down. Every entry that corresponds with a TRUE result in column B is being included in the sum.

In a similar way, you can use an IF() function or boolean expression with the same criteria as the SUMIF() function to see which values are included. For example, if your SUMIF() function is =SUMIF(A1:A4,">=3"), then you could use a formula like =(A1>=3) in B1 (and copied down) to see which entries were included in the sum.

Basically, once you understand how your chosen function chooses which values to include in its calculation, then follow up with a helper column (or even conditional format) that tests the same condition.

[JulianS96]

Ok well my example was obviously too simple.
What I mean is that say I have a SUMIF formula, and want to know exactly what values it is pulling out without having to do any extra calculations?
For example the function INDEX(MATCH or OFFSET( show the values used when you click "Evaluate Formula" but the SUMIF one does not and just shows the resulting sum.
Reason for asking this question?
I want to see if any erroneous results are being pulled from the SUMIF function. If not, then so be it, if yes then please tell me.
EDIT: Thanks Shorty, that may be a good idea for error checking and proofing.

[MrShorty]

For example the function INDEX(MATCH or OFFSET( show the values used when you click "Evaluate Formula" but the SUMIF one does not and just shows the resulting sum.
Reason for asking this question?
I want to see if any erroneous results are being pulled from the SUMIF function. If not, then so be it, if yes then please tell me.

To my knowledge, no, there is no built in way to "look under the hood" of Excel's built in functions to see what happens inside of the function. There have been a few situations where, like you, I have wanted to query a functionIFS() function to see what values it chose to include, but I have never found a utility (like the evaluate formula utility) that will readily do this. I think we are on our own to develop such a debugging tool, but, as my "helper column" suggests, this need not be a difficult programming exercise.

[JulianS96]

Ok Cheers anyway Boss