Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Old June 29th 09, 07:15 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
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  
Old June 29th 09, 07:37 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
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  
Old June 29th 09, 07:58 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
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  
Old June 29th 09, 08:22 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
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  
Old June 29th 09, 08:45 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Mar 2009
Posts: 8,521
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  
Old June 29th 09, 09:22 AM posted to microsoft.public.excel.misc
external usenet poster
 
First recorded activity by ExcelBanter: Jun 2007
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 05:06 AM.

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

About Us

"It's about Microsoft Excel"

 

Copyright © 2017