Remember Me?

 Sasikiran external usenet poster First recorded activity by ExcelBanter: Jun 2007 Posts: 104 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.

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

 Jacob Skaria external usenet poster First recorded activity by ExcelBanter: Mar 2009 Posts: 8,521 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.

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

 Sasikiran external usenet poster First recorded activity by ExcelBanter: Jun 2007 Posts: 104 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.

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

 francis external usenet poster First recorded activity by ExcelBanter: Mar 2009 Posts: 120 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)

--

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.

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

 Jacob Skaria external usenet poster First recorded activity by ExcelBanter: Mar 2009 Posts: 8,521 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.

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

 Sasikiran external usenet poster First recorded activity by ExcelBanter: Jun 2007 Posts: 104 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.

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

 Thread Tools Search this Thread Show Printable Version Search this Thread: Advanced Search Display Modes Linear Mode Switch to Hybrid Mode Switch to Threaded Mode Posting Rules Smilies are On [IMG] code is On HTML code is OffTrackbacks are On Pingbacks are On Refbacks are On Similar Threads Thread Thread Starter Forum Replies Last Post JoAnn New Users to Excel 1 April 22nd 08 08:57 PM [email protected] Excel Worksheet Functions 2 October 6th 06 09:56 PM laurie g Excel Worksheet Functions 3 September 11th 06 05:00 AM Tolga Excel Discussion (Misc queries) 6 July 5th 06 05:27 PM LeeHarris Excel Worksheet Functions 1 May 31st 06 10:23 AM

All times are GMT +1. The time now is 05:57 PM. Copyright ©2004-2020 ExcelBanter.