Home 
Search 
Today's Posts 
#1




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




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




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




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




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




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 
Display Modes  


Similar Threads  
Thread  Forum  
Problem using SUMPRODUCT  New Users to Excel  
Sumproduct problem...  Excel Worksheet Functions  
SUMPRODUCT PROBLEM  Excel Worksheet Functions  
sumproduct problem?  Excel Discussion (Misc queries)  
SUMPRODUCT problem  Excel Worksheet Functions 