COUNTIFS vs. SUMIFS: Sum of Verbal Phone Orders between 7:01am and 11:59pm

[Manny74]

Hi All,

I am having trouble creating a formula...

I need the sum of verbal phone orders between 7:01AM and 11:59PM....I counted 10 (data is below)

I started with the formula below, but maybe I should use SUMIFS? Or what formula can I use to get me the sum of verbal phone orders between 7:01AM and 11:59PM?


[TABLE="width: 61"]
<colgroup><col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <tbody>[TR]
[TD="width: 81, bgcolor: transparent"]

COUNTIFS (F:F,">0",D:D,">7:00AM",D:D,"<12:00AM")

[/TD]
[/TR]
</tbody>[/TABLE]




[TABLE="width: 327"]
<colgroup><col width="64" style="width: 48pt;"> <col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;" span="3"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"]B[/TD]
[TD="width: 103, bgcolor: transparent"]D[/TD]
[TD="width: 103, bgcolor: transparent"]E[/TD]
[TD="width: 64, bgcolor: transparent"]F[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="width: 103, bgcolor: transparent"]Order Date[/TD]
[TD="width: 103, bgcolor: transparent"]Order Time[/TD]
[TD="width: 103, bgcolor: transparent"]Pharm Orders [/TD]
[TD="width: 64, bgcolor: transparent"]Verbal Phone Orders
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]4[/TD]
[TD="width: 103, bgcolor: transparent"]2/12/2019[/TD]
[TD="width: 103, bgcolor: transparent"]6:48 AM[/TD]
[TD="width: 103, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]5[/TD]
[TD="width: 103, bgcolor: transparent"]2/12/2019[/TD]
[TD="width: 103, bgcolor: transparent"]6:49 AM[/TD]
[TD="width: 103, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]6[/TD]
[TD="width: 103, bgcolor: transparent"]2/12/2019[/TD]
[TD="width: 103, bgcolor: transparent"]6:54 AM[/TD]
[TD="width: 103, bgcolor: transparent"]2[/TD]
[TD="width: 64, bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]7[/TD]
[TD="width: 103, bgcolor: yellow"]2/27/2019[/TD]
[TD="width: 103, bgcolor: yellow"]8:36 AM[/TD]
[TD="width: 103, bgcolor: yellow"]1[/TD]
[TD="width: 64, bgcolor: yellow"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]8[/TD]
[TD="width: 103, bgcolor: yellow"]2/26/2019[/TD]
[TD="width: 103, bgcolor: yellow"]9:34 AM[/TD]
[TD="width: 103, bgcolor: yellow"]1[/TD]
[TD="width: 64, bgcolor: yellow"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]9[/TD]
[TD="width: 103, bgcolor: yellow"]2/26/2019[/TD]
[TD="width: 103, bgcolor: yellow"]9:40 AM[/TD]
[TD="width: 103, bgcolor: yellow"]1[/TD]
[TD="width: 64, bgcolor: yellow"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]10[/TD]
[TD="width: 103, bgcolor: yellow"]2/28/2019[/TD]
[TD="width: 103, bgcolor: yellow"]10:09 AM[/TD]
[TD="width: 103, bgcolor: yellow"]3[/TD]
[TD="width: 64, bgcolor: yellow"]3
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]11[/TD]
[TD="width: 103, bgcolor: yellow"]2/13/2019[/TD]
[TD="width: 103, bgcolor: yellow"]2:38 PM[/TD]
[TD="width: 103, bgcolor: yellow"]1[/TD]
[TD="width: 64, bgcolor: yellow"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]12[/TD]
[TD="width: 103, bgcolor: yellow"]2/12/2019[/TD]
[TD="width: 103, bgcolor: yellow"]7:00 PM[/TD]
[TD="width: 103, bgcolor: yellow"]1[/TD]
[TD="width: 64, bgcolor: yellow"]1[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]13[/TD]
[TD="width: 103, bgcolor: yellow"]2/5/2019[/TD]
[TD="width: 103, bgcolor: yellow"]8:14 PM[/TD]
[TD="width: 103, bgcolor: yellow"]2[/TD]
[TD="width: 64, bgcolor: yellow"]2[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]14[/TD]
[TD="width: 103, bgcolor: transparent"]2/7/2019[/TD]
[TD="width: 103, bgcolor: transparent"]3:33 PM[/TD]
[TD="width: 103, bgcolor: transparent"]9[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]15[/TD]
[TD="width: 103, bgcolor: transparent"]2/20/2019[/TD]
[TD="width: 103, bgcolor: transparent"]12:01 PM[/TD]
[TD="width: 103, bgcolor: transparent"]9[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]16[/TD]
[TD="width: 103, bgcolor: transparent"]2/22/2019[/TD]
[TD="width: 103, bgcolor: transparent"]10:50 AM[/TD]
[TD="width: 103, bgcolor: transparent"]9[/TD]
[TD="width: 64, bgcolor: transparent"]0[/TD]
[/TR]
</tbody>[/TABLE]

 

[James006]

Hi,

You could test following formula :

=SUMPRODUCT(($C$4:$C$16>TIME(7,1,0))*($C$4:$C$16<TIME(23,59,0))*($E$4:$E$16))

Hope this will help

 

[Fluff]

How about
=SUMIFS(F:F,D:D,">7:00",D:D,"<24:00")

 

[James006]

The Html codes did disrupt the formula ...

= Sumproduct(($c$4:$c$16 > time(7,1,0))*($c$4:$c$16 < time(23,59,0))*($e$4:$e$16))

HTH

 

[Manny74]

I forgot to mention, Verbal Phone Orders, should be greater than 0....I need sum of at least 1 verbal phone order or more, from 701am to 1159pm....

 

[Fluff]

Both formulae already do that.

 

[Manny74]

I see...Let me see if this one works.....

=SUMIFS(F:F,D:D,">7:00",D:D,"<24:00")

 

[Manny74]

I'm validating this formula.... =SUMIFS(F:F,D:D,">7:00",D:D,"<24:00")

It's mostly working, but in one scenario, it counted the order below, but the order time was 7:00am, so it shouldn't count it, but it did?

It should be counting orders after 7:00am....

[TABLE="width: 279"]
<colgroup><col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;" span="3"> <col width="64" style="width: 48pt;"> <tbody>[TR]
[TD="width: 103, bgcolor: transparent"]B[/TD]
[TD="width: 103, bgcolor: transparent"]D[/TD]
[TD="width: 103, bgcolor: transparent"]E[/TD]
[TD="width: 64, bgcolor: transparent"]F[/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]Order Date[/TD]
[TD="width: 103, bgcolor: transparent"]Order Time[/TD]
[TD="width: 103, bgcolor: transparent"]Pharm Orders [/TD]
[TD="width: 64, bgcolor: transparent"]Verbal Phone Orders [/TD]
[/TR]
[TR]
[TD="width: 103, bgcolor: transparent"]2/19/2019[/TD]
[TD="width: 103, bgcolor: yellow"]7:00 AM[/TD]
[TD="width: 103, bgcolor: transparent"]1[/TD]
[TD="width: 64, bgcolor: transparent"]1[/TD]
[/TR]
</tbody>[/TABLE]

 

[Fluff]

Check the exact value of that cell it's probably 7:00:01 or something similar.
to get round that try
=SUMIFS(F:F,D:D,">=7:01",D:D,"<24:00")