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]

I copied & pasted in the SUMPRODUCT formula you suggested in your 7:14pm post. It took it, but it returned 0. I did the “Evaluate Formula” to see if I could tell where the issue might be, but not being really familiar with the SUMPRODUCT function, I’m not 100% sure how to take the evaluation. Five steps in, I get this:

SUMPRODUCT({TRUE;TRUE;TRUE}*({FALSE;FALSE;FALSE})*(Apps!$H$3:$K$5=’Sales Stats’!C2)*Apps!$U$3:$U$5)

Should that second segment be false?

Two steps after that, I get:

SUMPRODUCT({0;0;0}*( Apps!$H$3:$K$5=’Sales Stats’!C2)*Apps!$U$3:$U$5)

Does this help in determining where my issue might be?

 

[RasGhul]

Sorry mate I went back to your original post and your End Date is in Sales Stat H not F, the FALSE indicates that it had no matches for the End Date.

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

 

[Aladin Akyurek]

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.
[…]

Control+shift+enter >> Press down the ontrol and the shift keys at the same tine while you hit the enter key. If done successfully, Excel itself puts a pair of { and } around the formula in recognition.

 

[RudeClown]

Aladin- I was able to get your array formula to go in, sorry, I was doing ctrl & holding, then shift & holding, then delete. Hitting all at the same time worked. But, it is giving me "0."

Ras- I pasted in the updated SUMPRODUCT formula, but it's giving me a #VALUE ! error. I tried to step through the "Evaluate Formula," but it eventually froze Excel a handful of steps in.

 

[Aladin Akyurek]

Aladin- I was able to get your array formula to go in, sorry, I was doing ctrl & holding, then shift & holding, then delete. Hitting all at the same time worked. But, it is giving me "0."
[…]

Post an Excel readable sample along with conditions and the result you want.

 

[RasGhul]

Hi Clown,

So Aladin's code works for me on my sheet also, but when I hit Enter by itself I also get the #value error

I also tried to force an error from my code, by removing criteria from the sheets but it always returns 0 but not #value error.

 

[RudeClown]

Sorry guys, I didn't think this would become such a pain in the butt! This is interesting.....I uploaded my file into Google Sheets with the intent of posting a link to it.....and when I look at it in Google Sheets, this formula:

<bdo dir="ltr">=SUMPRODUCT((Apps!$F$3:$F$5000>='Sales Stats'!$E$8)*(Apps!$F$3:$F$5000<='Sales Stats'!$H$8)*(Apps!$H$3:$K$5000='Sales Stats'!C2)*Apps!$U$3:$U$5000)

gives me the correct result of 2.5, but in Excel, it gives me a #Value ! error. All I did was upload the Excel file into Google Sheets I did not change anything. This is my very first time doing anything on Google sheets....any idea why there might be a discrepancy between what it gives, vs what Excel gives?
</bdo>
If I did this right, here's a link to my document in Excel form:

https://docs.google.com/spreadsheet...1WU6ZHYuwvA1Rv9fxB5S4giD6RjRR/pub?output=xlsx

The formula I'm after would go in Sales States!S13, though I would be adapting it to the other criteria you'll see on my sheet. The random stuff in N:10 - S:10 are just some of the formulas I've been playing with, they won't stay there once I get a working formula. If you can get to the link, request access, and I'll approve it. My original is on my hard drive, so changes can be made to the uploaded version. Thank you again guys for helping me out!

 

[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+0))))

 

[RudeClown]

Aladin - I pasted in your last formula and used ctrl+shift+enter, and it accepted it as a formula, but it returns a #VALUE ! error.

 

[Aladin Akyurek]

Aladin - I pasted in your last formula and used ctrl+shift+enter, and it accepted it as a formula, but it returns a #VALUE ! error.

You don't have to paste anything...

You have to just edit the formula of P10 in the sheet Sales Stats from my original suggestion:

=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))))​

to:

=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+0))))


While in the cell, apply control+shift+enter. The cell will display 2.5 as result.​

This +0 is needed because your Apps!$U$2:$U$200 contains text numbers, not true numbers. This addition converts text number to true numbers.

By the way, you create those text numbers yourself with the formula

​

=IF(T3=1,"1",IF(T3=2,".5",IF(T3=3,".33",IF(T3=4,".25",""))))

that you have in column U of Apps. If you remove double quotes around 1, .5, .33, and .25, you would get true numbers.

​