Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |