=sumifs question on formula to get total by month

[steve400243]

Hello Forum, I am in need of getting the totals by month for service types. I have a tab named Spot Quotes, in column R is the Service type, Col 'N' has the date and time completed like this '2/1/2018 13:00'

My total tab is named Total Quotes. Starting in cell H4 would be the total for the month (Feb-18) for service type 'AE'

I4 would be for service type 'AI' and so on thru P4.

A4 is the month Column Jan-18, A5 Feb-18. etc.
Below are the service types that can be selected from a drop down in column R from the 'Spot Quotes' tab
[TABLE="width: 630"]
<tbody>[TR]
[TD="class: xl69, width: 70"]AE[/TD]
[TD="class: xl66, width: 70"]AI[/TD]
[TD="class: xl66, width: 70"]OE[/TD]
[TD="class: xl66, width: 70"]OI[/TD]
[TD="class: xl66, width: 70"]Inland[/TD]
[TD="class: xl66, width: 70"]Surface[/TD]
[TD="class: xl66, width: 70"]Origin / Destination[/TD]
[TD="class: xl67, width: 70"] F/F[/TD]
[TD="class: xl68, width: 70"]USI / ER[/TD]
[/TR]
</tbody>[/TABLE]

Please let me know your ideas, I dont know how to get the formula working for this. Thanks for all help.

 

[Aladin Akyurek]

It's hard to read what is required. How or in which way are Total Quotes and Spot Quotes are related? Are we counting or summing?

 

[steve400243]

Thanks for looking Aladin - Here is what I have so far, And is not working. Counting the total for each service type by month.

Starting in Cell H4

=SUMIFS('Spot Quotes'!R:R,”AE”,Spot Quotes'!$N:$N,<"&EOMONTH($A4,0)+1)

On the "Spot Quotes" Tab - Column R is the service type. Column N is date completed.


A4 is on the "Total Quotes" Tab as 1/1/2018, A5 would be 2/01/2018

 

[Aladin Akyurek]

What do you have in R?

 

[steve400243]

R would be any one of the service types, selected from a drop down box. AE, AI, etc

 

[steve400243]

"R" is the service type selected from a drop down. Here is what I use to get the total, but I need it by month.

=COUNTIFS('Spot Quotes'!R:R,"AE")

 

[Aladin Akyurek]

"R" is the service type selected from a drop down. Here is what I use to get the total, but I need it by month.

=COUNTIFS('Spot Quotes'!R:R,"AE")

We are doing thus counting, not summing.

Try...

=COUNTIFS('Spot Quotes'!$R:$R,"AE",'Spot Quotes'!$N:$N,">="&A4,'Spot Quotes'!$N:$N,"<="&EOMONTH($A4,0))

 

[steve400243]

Thank you, Aladin, I got it working as needed, with one small change at the end of the formula. Thank you for your time.

=COUNTIFS('Spot Quotes'!$R:$R,"AE",'Spot Quotes'!$N:$N,">="&A4,'Spot Quotes'!$N:$N,"<="&EOMONTH($A4,1))

 

[steve400243]

This was the change that I made -

[COLOR=#333333]<="&EOMONTH($A4,0)+1)[/COLOR]

 

[Aladin Akyurek]

Thank you, Aladin, I got it working as needed, with one small change at the end of the formula. Thank you for your time.

=COUNTIFS('Spot Quotes'!$R:$R,"AE",'Spot Quotes'!$N:$N,">="&A4,'Spot Quotes'!$N:$N,"<="&EOMONTH($A4,1))

You are welcome.