Sum cells included in time frame

[Zanael]

Hello there !

I have a very simple excel file with time in column A (format hh:MM AM/PM) and dollar value in column B (pretty much time of transaction and amount received)

I m trying to add cells from column B ($) that are included between 10:00 AM and 10:59AM, then add those between 11:00AM and 11:59 AM, etc...

I ve looked for an answer in older posts; solution seems to include "sumproduct"...but i m having a hard time.

Any help would be greatly appreciated !!!


Aurélien

 

[FDibbins]

Try something like this...

=sumifs(sum-range,time-range, ">=10/24",time-range,"<11/24")

 

[CyrusTheVirus]

Hi Zanael,

Maybe something like the below setup could help?

F2:

=SUMPRODUCT($B$2:$B$19,--(($A$2:$A$19>=$D2)*($A$2:$A$19<=$E2)))

Or like FDibbins suggested F2:

=SUMIFS($B$2:$B$19,$A$2:$A$19,">="&$D2,$A$2:$A$19,"<="&$E2)

Time	Amount		Start Time	End Time	Sum of Amount
12:00 AM	974.00		12:00 AM	12:59 AM	$ 1,502.00
12:01 AM	294.00		1:00 AM	1:59 AM	$ 1,663.00
12:59 AM	234.00		2:00 AM	2:59 AM	$ 1,462.00
1:00 AM	195.00		3:00 AM	3:59 AM	$ -
1:01 AM	652.00		4:00 AM	4:59 AM	$ -
1:59 AM	816.00		5:00 AM	5:59 AM	$ -
2:00 AM	257.00		6:00 AM	6:59 AM	$ -
2:01 AM	458.00		7:00 AM	7:59 AM	$ -
2:59 AM	747.00		8:00 AM	8:59 AM	$ -
12:00 PM	73.00		9:00 AM	9:59 AM	$ -
12:01 PM	675.00		10:00 AM	10:59 AM	$ -
12:59 PM	791.00		11:00 AM	11:59 AM	$ -
1:00 PM	950.00		12:00 PM	12:59 PM	$ 1,539.00
1:01 PM	633.00		1:00 PM	1:59 PM	$ 2,570.00
1:59 PM	987.00		2:00 PM	2:59 PM	$ 1,439.00
2:00 PM	814.00		3:00 PM	3:59 PM	$ -
2:01 PM	625.00		4:00 PM	4:59 PM	$ -
2:59 PM	249.00		5:00 PM	5:59 PM	$ -
			6:00 PM	6:59 PM	$ -
			7:00 PM	7:59 PM	$ -
			8:00 PM	8:59 PM	$ -
			9:00 PM	9:59 PM	$ -
			10:00 PM	10:59 PM	$ -
			11:00 PM	11:59 PM	$ -

<colgroup><col><col><col><col><col><col></colgroup><tbody>
</tbody>

 

[Zanael]

That sumifs formula works like a charm, thank you guys so much!

 

[FDibbins]

Happy to help