Lengthy SUMIFS Formula Assistance

[RudeClown]

Hi all,

I'm working on a tracking spreadsheet for # of contracts written by salespeople, and I'm having difficulty with the formula to add up the number of contracts per salesperson based on a given date range (partial credit for split sales). It seems like it has to be something simple I'm missing, I've broken down the formula into sections to see if each works individually, and they do not. I'm working with with the salesperson "Brad" to create the formula. My expectation is that it adds the values in "Credit per Contract" column for any row where "Brad" appears in any of the four Salespeople columns, and has a Contract Date within the given date range. Here's the pertinent data:

Worksheet "Apps"
Column F (Contract Date)
Row 3: 7/1/18
Row 4: 6/1/18
Row 5: 6/30/17

Column H (Salesperson #1 )
Row 3: Brad
Row 4: Pam
Row 5: Brad

Column I & J (Salesperson 2 & 3)
no data

Column K (Salesperson #4 )
Row 3: no data
Row 4: Brad
Row 5: no data

Column U (Credit per contract)
Row 3: 1
Row 4: .5
Row 5: 1

Worksheet "Sales Stats" (this is where the formula is located I am working on)
Column C
Row 2: Brad

Column E
Row 8 ("from" date to search by): 1/1/2017

Column H
Row 8 ("to" date to search by): 7/8/2018

Here's the full formula I have which is currently returning "0". It should be returning "2.5," because Brad has two full contracts, and one split contract:

=IF($C$2="","",(SUMIFS(Apps!U:U,Apps!F:F,">="&$E$8,Apps!F:F,"<="&$H$8,Apps!H:H,"="&$C$2)+SUMIFS(Apps!U:U,Apps!F:F,">="&$E$8,Apps!F:F,"<="&$H$8,Apps!I:I,"="&$C$2)+SUMIFS(Apps!U:U,Apps!F:F,">="&$E$8,Apps!F:F,"<="&$H$8,Apps!J:J,"="&$C$2)+SUMIFS(Apps!U:U,Apps!F:F,">="&$E$8,Apps!F:F,"<="&$H$8,Apps!K:K,"="&$C$2)))

I broke it down to just count the records in the "Salesperson 1" column (H), and this also returns "0", it should return "2" because Brad is noted on two contracts in column H only:

=SUMIFS(Apps!U:U,Apps!F:F,">="&E8,Apps!F:F,"<="&H8,Apps!H:H,"="&C2)

Obviously what I am trying to do is look at Salesperson 1 column for "Brad", check the date range in F against what has been entered by the user in Sales Stats E8 & H8, and if Brad is present, add the value in column U. Then it checks Salesperson #2 column for the same, then #3 , then #4 , then on to the next row to check the next contract to see if Brad is present there and add the value in U if so, etc all the way down through the date range.

Any suggestions would really be appreciated!!

 

[RasGhul]

Hi Clown,

I built a sheet as per the above columns & sheet names and this returned 2.5 for me;

=IF(C2="","",SUMIFS(Apps!$U$3:$U$5,Apps!$H$3:$H$5,'Sales Stats'!C2,Apps!$F$3:$F$5,">="&'Sales Stats'!$E$8,Apps!$F$3:$F$5,"<="&'Sales Stats'!$F$8)+SUMIFS(Apps!$U$3:$U$5,Apps!$K$3:$K$5,'Sales Stats'!C2,Apps!$F$3:$F$5,">="&'Sales Stats'!$E$8,Apps!$F$3:$F$5,"<="&'Sales Stats'!$F$8))

 

[RudeClown]

Thank you for taking the time to take a look! I appreciate it! I copied and pasted the code that you noted, and it gave me zero once again. The values in column "U" are derived from formulas in those cells, could that be affecting the reference to those cells? I've never had that happen before, so I wouldn't think so. I wonder if there's some other way that a piece of my data is entered that could have an effect on references to the values in those cells? I'm at a loss why it doesn't work, especially if your formula worked for you, but isn't working for me.

 

[RasGhul]

Its most likely the name criteria if Brad had spaces after it then that would not match your criteria on your sales person sheet. Check your Apps H & K columns. If your unsure of the salesperson column for other naughty spaces use =TRIM(CLEAN(H3)) to create a "Clean" column for your sumifs.

Also you can check if your credit points are numeric by running =ISNUMBER(U3) alongside if room. These should be true.

 

[RasGhul]

Here's a copy of my working code with your sample data.

https://docs.google.com/spreadsheet...OK83W3OTzexyux6Z7AH8eYm3uA/edit#gid=460576851

 

[Aladin Akyurek]

Control+shift+enter, not just enter:

=SUM(IF(Apps!$H$2:$K$200="brad",IF(Apps!$F$2:$F$200>=$E$8,IF(Apps!$F$2:$F$200<=$H$8,Apps!$U$2:$U$200))))

Is this what you are after?
​

​

 

[RasGhul]

This also works, thanks Aladin for giving me the idea on the whole range;

=SUMPRODUCT((Apps!H3:K5='Sales Stats'!C2)*Apps!U3:U5)

 

[RudeClown]

Thanks guys! I checked for spaces in all occurrences of the word "Brad," and there weren't any. I also double checked to make sure all numerical columns were formatted as numbers. I entered Aladin's formula and hit ctrl+shift+enter, but it simply displayed the code in the cell instead of the result of the formula. I tried to access the spreadsheet on Google Docs, and I requested permission to access it. The SumProduct code gave me 2.5, but it doesn't take the date range into account. I need it to only search between the given date range provided in SalesStats!E8 and SalesStats!H8. I've not used SUMPRODUCT before....can it be adapted with the date range? Also, the data entered is simply test data, ultimately I need the formula to be able to search the entire length of the spreadsheet for the date range given. There will be hundreds and eventually thousands of rows, so I really need to be able to use references to the entire columns (U:U, H:H, etc), rather than referencing the specific cells for the test data. I also tried this prior to posting here to try and check all 4 Salesperson columns in one segment, but it gave a #Value error, which led me to try doing each column individually and adding them together:

=IF(C2="","",(SUMIFS(Apps!U:U,Apps!F:F,">="&$E$8,Apps!F:F,"<="&$H$8,Apps!H:K,"="&$C$2)))

This (in theory) would take each row on the worksheet one by one, first check the date range provided in Sales Stats!E8 & H8, and if the row is within the given date range, it will check Apps!H, I, J, & K for the name in Sales Stats!C2, in case of the first salesperson, it is "Brad." If "Brad" is present in any of those 4 columns, then it would add the value in Apps!U to the overall result of the formula which will display after checking all rows. It seems like I should be able to use this shortened version of the formula, but like I said, since it didn't work to start with, I went the longer route. Any additional thoughts?

 

[RasGhul]

Hi Clown,

Sumproduct with Date criteria for any occurrences of Brad anywhere in H-K ranges;

=SUMPRODUCT((Apps!$F$3:$F$5>='Sales Stats'!$E$8)*(Apps!$F$3:$F$5<='Sales Stats'!$F$8)*(Apps!$H$3:$K$5='Sales Stats'!C2)*Apps!$U$3:$U$5)

 

[RasGhul]

You can also create name ranges for Dates, Salespersons & Credits;

=SUMPRODUCT((dates>='Sales Stats'!$E$8)*(dates<='Sales Stats'!$F$8)*(salespersons='Sales Stats'!C2)*credits)