Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 104
Default 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.


  #2   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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.


  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 104
Default 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.


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 120
Default 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.


  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 8,520
Default 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.




  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 104
Default 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.


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem using SUMPRODUCT JoAnn New Users to Excel 1 April 22nd 08 08:57 PM
Sumproduct problem... [email protected] Excel Worksheet Functions 2 October 6th 06 09:56 PM
SUMPRODUCT PROBLEM laurie g Excel Worksheet Functions 3 September 11th 06 05:00 AM
sumproduct problem? Tolga Excel Discussion (Misc queries) 6 July 5th 06 05:27 PM
SUMPRODUCT problem LeeHarris Excel Worksheet Functions 1 May 31st 06 10:23 AM


All times are GMT +1. The time now is 12:16 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"