Sum by range of time

[rocketD]

Hello,

I have a column of values (A) and a column of times (B). The times
fall between 8:00am and 10:00am, but I'm only interested in the values
between 9:00am and 10:00am. I want to sum all values in column A
whose time values in column B are >=9:00 and <=9:59. I've tried
sumif, nested if, or() and and() functions, and I can't get anything
to work properly. Can anyone help?

Thanks!

 

[Roger Govier]

Hi

One way
=SUMPRODUCT(($B$2:$B$100>=TIME(9,0,0))*$B$2:$B$100<=TIME(9,59,0))*$A$2:$A$100)

--
Regards
Roger Govier

Hello,

I have a column of values (A) and a column of times (B). The times
fall between 8:00am and 10:00am, but I'm only interested in the values
between 9:00am and 10:00am. I want to sum all values in column A
whose time values in column B are >=9:00 and <=9:59. I've tried
sumif, nested if, or() and and() functions, and I can't get anything
to work properly. Can anyone help?

Thanks!

__________ Information from ESET Smart Security, version of virus
signature database 4751 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 4751 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Per Jessen]

Hi

Try this:

=SUMIF(B2:B10,">=" & TIMEVALUE("09:00"),A2:A10)-SUMIF(B2:B10,">=" &
TIMEVALUE("10:00"),A2:A10)

or this:

=SUMPRODUCT(--(B2:B10>=TIMEVALUE("09:00")),--(B2:B10<TIMEVALUE("10:00")),A2:A10)

Regards,
Per

 

[rocketD]

Hi

Try this:

=SUMIF(B2:B10,">=" & TIMEVALUE("09:00"),A2:A10)-SUMIF(B2:B10,">=" &
TIMEVALUE("10:00"),A2:A10)

or this:

=SUMPRODUCT(--(B2:B10>=TIMEVALUE("09:00")),--(B2:B10<TIMEVALUE("10:00")),A2­:A10)

Regards,
Per

"rocketD" <[email protected]> skrev i meddelelsen





- Show quoted text -

The second option worked very well. I checked it by making a series
of columns that teased out the values I want; this is great, because I
can use this array. Thanks for your help everyone!

 

[rocketD]

Hi

One way
=SUMPRODUCT(($B$2:$B$100>=TIME(9,0,0))*$B$2:$B$100<=TIME(9,59,0))*$A$2:$A$1­00)

--
Regards
Roger Govier











__________ Information from ESET Smart Security, version of virus signature database 4751 (20100107) __________

The message was checked by ESET Smart Security.

http://www.eset.com- Hide quoted text -

- Show quoted text -

Thanks Roger, I tried this and I can only get #N/A. the second option
in the following post worked, however.