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?

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

<colgroup><col width="81" style="width: 61pt; mso-width-source: userset; mso-width-alt: 2962;"> <tbody> </tbody>

	B	D	E	F
	Order Date	Order Time	Pharm Orders	Verbal Phone Orders
4	2/12/2019	6:48 AM	1	1
5	2/12/2019	6:49 AM	1	1
6	2/12/2019	6:54 AM	2	2
7	2/27/2019	8:36 AM	1	1
8	2/26/2019	9:34 AM	1	1
9	2/26/2019	9:40 AM	1	1
10	2/28/2019	10:09 AM	3	3
11	2/13/2019	2:38 PM	1	1
12	2/12/2019	7:00 PM	1	1
13	2/5/2019	8:14 PM	2	2
14	2/7/2019	3:33 PM	9	0
15	2/20/2019	12:01 PM	9	0
16	2/22/2019	10:50 AM	9	0

<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> </tbody>

 

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

B	D	E	F
Order Date	Order Time	Pharm Orders	Verbal Phone Orders
2/19/2019	7:00 AM	1	1

<colgroup><col width="103" style="width: 77pt; mso-width-source: userset; mso-width-alt: 3766;" span="3"> <col width="64" style="width: 48pt;"> <tbody> </tbody>

 

[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")