Problem with SUMPRODUCT
 

Dear,

I have the data in a sheet "Mon" and i would require to calcuate the total
of the numbers in column H if the corresponding value in column B is 8:00 AM.

I am trying to use the below formula. Please help me in correcting the same.

=SUMPRODUCT((Mon!B$2:B$10000=--"8:00 AM")*(Mon!H$2:H$10000=--1))

Thanks in advance.

Problem with SUMPRODUCT
 

Try

=SUMPRODUCT(--(Mon!B$2:B$10000=TIMEVALUE("8:00 AM")),Mon!H$2:H$10000)

Please note that if the time entry is 08:00:01 still it is displayed as 8:00
AM but the formula will not count. To handle that use

=SUMPRODUCT(--(HOUR(Mon!B$2:B$10000)=8),Mon!H$2:H$10000)

If this post helps click Yes
---------------
Jacob Skaria


"Sasikiran" wrote:

Dear,

I have the data in a sheet "Mon" and i would require to calcuate the total
of the numbers in column H if the corresponding value in column B is 8:00 AM.

I am trying to use the below formula. Please help me in correcting the same.

=SUMPRODUCT((Mon!B$2:B$10000=--"8:00 AM")*(Mon!H$2:H$10000=--1))

Thanks in advance.

Problem with SUMPRODUCT
 

Dear Jacob,

The first one is working... Thanks for that..

Can the second formula be altered in such a way that it will count the
number if the hour value is 8, minute values is 00 and the seconds value is
anything between 1 and 59?

=SUMPRODUCT(--(HOUR(Mon!B$2:B$10000)=8),Mon!H$2:H$10000)



"Jacob Skaria" wrote:

Try

=SUMPRODUCT(--(Mon!B$2:B$10000=TIMEVALUE("8:00 AM")),Mon!H$2:H$10000)

Please note that if the time entry is 08:00:01 still it is displayed as 8:00
AM but the formula will not count. To handle that use

=SUMPRODUCT(--(HOUR(Mon!B$2:B$10000)=8),Mon!H$2:H$10000)

If this post helps click Yes
---------------
Jacob Skaria


"Sasikiran" wrote:

Dear,

I have the data in a sheet "Mon" and i would require to calcuate the total
of the numbers in column H if the corresponding value in column B is 8:00 AM.

I am trying to use the below formula. Please help me in correcting the same.

=SUMPRODUCT((Mon!B$2:B$10000=--"8:00 AM")*(Mon!H$2:H$10000=--1))

Thanks in advance.

Problem with SUMPRODUCT
 

for a single criteria, you could use SUMIF

=SUMIF(Mon!B$1:B$10000,TIME(8,0,0),Mon!H$1:H$10000 )

to use SUMPRODUCT, try this
=SUMPRODUCT(--(Mon!B$1:B$10000=TIME(8,0,0)),Mon!H$1:H$10000)

--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another



"Sasikiran" wrote:

Dear,

I have the data in a sheet "Mon" and i would require to calcuate the total
of the numbers in column H if the corresponding value in column B is 8:00 AM.

I am trying to use the below formula. Please help me in correcting the same.

=SUMPRODUCT((Mon!B$2:B$10000=--"8:00 AM")*(Mon!H$2:H$10000=--1))

Thanks in advance.

Problem with SUMPRODUCT
 

Try

=SUMPRODUCT(--(TEXT(Mon!B$2:B$10000,"hh:mm")="08:00"),Mon!H$2:H$ 10000)

If this post helps click Yes
---------------
Jacob Skaria


"Sasikiran" wrote:

Dear Jacob,

The first one is working... Thanks for that..

Can the second formula be altered in such a way that it will count the
number if the hour value is 8, minute values is 00 and the seconds value is
anything between 1 and 59?

=SUMPRODUCT(--(HOUR(Mon!B$2:B$10000)=8),Mon!H$2:H$10000)



"Jacob Skaria" wrote:

Try

=SUMPRODUCT(--(Mon!B$2:B$10000=TIMEVALUE("8:00 AM")),Mon!H$2:H$10000)

Please note that if the time entry is 08:00:01 still it is displayed as 8:00
AM but the formula will not count. To handle that use

=SUMPRODUCT(--(HOUR(Mon!B$2:B$10000)=8),Mon!H$2:H$10000)

If this post helps click Yes
---------------
Jacob Skaria


"Sasikiran" wrote:

Dear,

I have the data in a sheet "Mon" and i would require to calcuate the total
of the numbers in column H if the corresponding value in column B is 8:00 AM.

I am trying to use the below formula. Please help me in correcting the same.

=SUMPRODUCT((Mon!B$2:B$10000=--"8:00 AM")*(Mon!H$2:H$10000=--1))

Thanks in advance.

Problem with SUMPRODUCT
 

Thanks a ton Jacob... :)

"Jacob Skaria" wrote:

Try

=SUMPRODUCT(--(TEXT(Mon!B$2:B$10000,"hh:mm")="08:00"),Mon!H$2:H$ 10000)

If this post helps click Yes
---------------
Jacob Skaria


"Sasikiran" wrote:

Dear Jacob,

The first one is working... Thanks for that..

Can the second formula be altered in such a way that it will count the
number if the hour value is 8, minute values is 00 and the seconds value is
anything between 1 and 59?

=SUMPRODUCT(--(HOUR(Mon!B$2:B$10000)=8),Mon!H$2:H$10000)



"Jacob Skaria" wrote:

Try

=SUMPRODUCT(--(Mon!B$2:B$10000=TIMEVALUE("8:00 AM")),Mon!H$2:H$10000)

Please note that if the time entry is 08:00:01 still it is displayed as 8:00
AM but the formula will not count. To handle that use

=SUMPRODUCT(--(HOUR(Mon!B$2:B$10000)=8),Mon!H$2:H$10000)

If this post helps click Yes
---------------
Jacob Skaria


"Sasikiran" wrote:

Dear,

I have the data in a sheet "Mon" and i would require to calcuate the total
of the numbers in column H if the corresponding value in column B is 8:00 AM.

I am trying to use the below formula. Please help me in correcting the same.

=SUMPRODUCT((Mon!B$2:B$10000=--"8:00 AM")*(Mon!H$2:H$10000=--1))

Thanks in advance.