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!!

 

[RudeClown]

Hey guys, thank you! Aladin, your comment about the text number solved the dilemma. That's why yours were working, and mine was not. Several of the formulas you guys presented plus the original formula I started off with work now after removing the quotes around the digits in the U column. Ras, you called me on that in one of your first posts, but I thought simply having the column formatted as a number was sufficient, I didn't realize it was still writing the numbers as text. I tested by adding some addt'l items on my Apps! page, and the numbers were correct in those couple formulas. I truly appreciate all your help!!!