SUMIFS and SUMPRODUCT formula not calculating all values

[dlsmith36]

I have a report that should calculate won sales of reps in our company based on the date the contract was processed is where the contract totals should appear in the report for the entire calendar year 2018.

Based on my data for a particular rep, the total amount is supposed to be $14,765, Excel is only calculating $10,361 with the following SUMIF formula:

=SUMIFS(Opportunities!U:U,Opportunities!K:K,">="&$A20,Opportunities!K:K,"<="&$B20,Opportunities!E:E,$A$7,Opportunities!F:F,"="&$D$6)


Data tab with column definitions
U column = Total Sale
K column = Processed date
E column = Sales rep name
F column = Sales stage

Report tab with row definitions
A Row = start date
B Row = end date
A7 = Sales rep name

D6 = Sales stage of WON


I have tried changing the references from whole column to absolute but that didn't work either.

Any ideas on what I am doing wrong is greatly appreciated.

 

[Special-K99]

Re: Any Ideas...SUMIFS and SUMPRODUCT formula not calculating all values

Why have you got F:F,"="&$D6 at the end of the formula?
Shouldnt that just be F:F,$D$6

Maybe the values in column U are text and not numbers?

 

[dlsmith36]

Re: Any Ideas...SUMIFS and SUMPRODUCT formula not calculating all values

Values are all numbers and F:F,"="&$D6 is referencing the sales stage column for the WON value that equals the WON sales totals on the report.

 

[Special-K99]

Re: Any Ideas...SUMIFS and SUMPRODUCT formula not calculating all values

The only way you're gonna sort this out is to analyse the data.

Post the spreadsheet, remove any sensitive data, create a mockup example if necessary.
You cant attach files on this forum. There are tools on this forum for adding small spreadsheet images

https://www.mrexcel.com/forum/about-board/508133-attachments.html

Or upload the file to an online storage site and post a link to it, though some people may not download the file for fear of viruses.

 

[Special-K99]

Re: Any Ideas...SUMIFS and SUMPRODUCT formula not calculating all values

Some thoughts.

1. Limit the SUMIF to the first half a dozen rows.
Manually calculate the total and see if it's correct.
If it is then it's somewhere in the rest of your data that's causing the problem.

2. Formula looks ok to me, as long as A20 and B20 are dates

3. Remove the SUMIF conditions one at a time until you get the total, then you know what condition is causing the problem, then examine that data.

 

[dlsmith36]

Re: Any Ideas...SUMIFS and SUMPRODUCT formula not calculating all values

Here is the link to my document: https://1drv.ms/x/s!Atj7RD3wjbBSh2utOdH-b7iBOBpH

Use Dawson tab, column D for WON revenue. Total should be $14765, current formula calculating $10361. Rows 20 should be $1575 and row 24 should be $2829.

Use Opportunities tab for data formula is referencing.

Thanks in advance.

 

[Special-K99]

Re: Any Ideas...SUMIFS and SUMPRODUCT formula not calculating all values

Can't open workbook

"Sorry, we can't open your workbook in Excel Online because it exceeds the 5 MB file size limit."

 

[dlsmith36]

Re: Any Ideas...SUMIFS and SUMPRODUCT formula not calculating all values

Sorry about that. File updated. Link: https://1drv.ms/x/s!Atj7RD3wjbBSh22vcwCdb0087nXj

Use Dawson tab, column D for WON revenue. Total should be $14765, current formula calculating $10361. Rows 20 should be $1575 and row 24 should be $2829.

Use Opportunities tab for data formula is referencing.