SumIf Formula Snafu

[kellywy]

Hi all --

This is sort of an update on a previous problem, because the formula that I had worked out with your help has gone wonky on me. !? What I need is to figure out where I've screwed it up.

The calculation I need:
- Total the sales figures in K18:K250 - based on 2 parameters:
- #1 : The month they took place, and (Dates are in H18:H250)
- #2 : The Sales Agent who made the sale. (Sales Agent initials are in I18:I250)

I have my calculating dates (1st of the month in AE3; last day of the month in AF3).

The formula I currently have is this (given the Sales Agent initials as "EL"):

=SUMIFS($K$18:$K$250,$H$18:$H$250,">="&$AE3,$H$18:$H$250,"<"&EDATE($AF3,1),$I18:$I250,"EL")

For some reason, it's calculating double the sales figures.
So, while EL has actually sold $59,319 in September 2017 - the total it shows for my formula is $91,838. ?

Anyone have any suggestions on where I confabulated my formula? :-\

Appreciate any help in advance.......(and I mean you, Ford!) haha!

Kelly

 

[AhoyNC]

Kind of a guess here, but if AF3 is the last day of the month then EDATE($AF3,1) would return the last day in the following month which would give you sales for a 2 month period instead of 1 month. Hard to say though not knowing what dates you have in AE3 & AF3.

 

[kellywy]

Ooooohhhhhh! I wondered if that might have something to do with it. Although I had tried eliminating the 1 previously, and was still having calc problems. ****. Which I still am, I see.

AE3 is September 1, 2017 - AF 3 is September 30, 2017.
So.....can you tell me how would I fix that?

Kelly

 

[AhoyNC]

Try one of these:

SUMIFS($K$18:$K$250,$H$18:$H$250,">="&$AE3,$H$18:$H$250,"<="&EDATE($AE3,1),$I18:$I250,"EL")

or

[TABLE="width: 732"]
<colgroup><col width="732"></colgroup><tbody>[TR]
[TD="width: 732"]SUMIFS($K$18:$K$250,$H$18:$H$250,">="&$AE3,$H$18:$H$250,"<="&$AF3,$I18:$I250,"EL")[/TD]
[/TR]
</tbody>[/TABLE]

<tbody>
</tbody>

[TABLE="width: 674"]
<tbody>[TR]
[TD="width: 674"][/TD]
[/TR]
</tbody>[/TABLE]

 

[kellywy]

Got it! Thank you so much! (I figured it out just before your reply - ha! maybe that means I'm learning something....) Appreciate your help! I've been stymied on this for a few days.

 

[AhoyNC]

You're welcome. Glad you got it figured out. Thanks for the feedback.