SumIf Returning Wrong Values

[swede5150]

Hi
I am using Excel to define how much income per contract has been received against what was expected to be received.
Sporadically, the wrong value of the actual received amount is being returned but can't understand why ?
My formula for this is;
=IF(C82="","",SUMIF('Commission Data'!A:Q,C82,'Commission Data'!Q:Q))

Can anyone offer any guidance,

Thanks

 

[Joe4]

Welcome to the Board!

I do not think you are using the SUMIF formula correctly. It looks like the range you are checking is a multi-column range (A:Q), which doesn't make much sense. Usually, you check one column per row, so I would expect that to be a single column reference (see: SUMIF function - Microsoft Support).

Perhaps what you are trying to do will make more sense if you show us some sample data along with your expected results. MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[RoryA]

Further to what Joe said, SUMIF requires both ranges to be the same size and shape. If the sum range size/shape is different to the criteria range, it will automatically be adjusted to match, so your formula is actually:

SUMIF('Commission Data'!A:Q,C82,'Commission Data'!Q:AG)

where a criteria match in column A will sum column Q and a match in B sums column R and so on.

 

[swede5150]

Thanks both for your explanations, amended the formula to

=IF(C2="","",SUMIF('Commission Data'!A:A,C2,'Commission Data'!Q:Q))

and is reading to the £0.01.

Thanks again

 

[Joe4]

Thanks both for your explanations, amended the formula to

=IF(C2="","",SUMIF('Commission Data'!A:A,C2,'Commission Data'!Q:Q))

and is reading to the £0.01.

Thanks again

I am not sure that you are saying.
Is that what you expect?
Is it working the way you want now?

If not, please do what I asked and post a sample of your data and expected results so we can see exactly what data you are working with and what you are trying to get out of it.

 

[swede5150]

Sorry, yes mean to say, all fixed and working

 

[Joe4]

Excellent, glad to hear it!