Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Dear,
I am trying to work on a formula which gives the count within a date range with few specifications in some of the columns. The data is in Sheet1 and I want to insert the formula in Sheet2. From the raw data, I would like to get a formula which calculates the number of times the specified categories are present in these columns. The date is in m/d/yyyy h:mm format. Within the specified date range in column B, the formula should search for "Audio / Telephony / VOIP" in column O, "Audio Broadcasting" in column P, "Quality" in column Q and gives the count if all the criteria is matched. Please help =SUMPRODUCT((Sheet1!B:B=--"2/5/2010 0:00:00 AM"), (Sheet1!B:B<=--"2/10/2010 11:59:59 PM"), (Sheet1!O:O=--"Audio / Telephony / VOIP"), (Sheet1!P:P=--"Audio Broadcasting"), (Sheet1!Q:Q=--"Quality")) |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Hi,
try =SUMPRODUCT((Sheet1!B:B="2/5/2010 0:00:00 AM")*(Sheet1!B:B<="2/10/2010 11:59:59 PM")*(Sheet1!O:O="Audio / Telephony / VOIP")*(Sheet1!P:P="Audio Broadcasting")*(Sheet1!Q:Q="Quality")) "Sasikiran" wrote: Dear, I am trying to work on a formula which gives the count within a date range with few specifications in some of the columns. The data is in Sheet1 and I want to insert the formula in Sheet2. From the raw data, I would like to get a formula which calculates the number of times the specified categories are present in these columns. The date is in m/d/yyyy h:mm format. Within the specified date range in column B, the formula should search for "Audio / Telephony / VOIP" in column O, "Audio Broadcasting" in column P, "Quality" in column Q and gives the count if all the criteria is matched. Please help =SUMPRODUCT((Sheet1!B:B=--"2/5/2010 0:00:00 AM"), (Sheet1!B:B<=--"2/10/2010 11:59:59 PM"), (Sheet1!O:O=--"Audio / Telephony / VOIP"), (Sheet1!P:P=--"Audio Broadcasting"), (Sheet1!Q:Q=--"Quality")) |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Try it like this:
=SUMPRODUCT(--(Sheet1!B:B=--"2/5/2010"),--(Sheet1! B:B<--"2/11/2010"),--(Sheet1!O:O="Audio / Telephony / VOIP"),--(Sheet1! P:P="Audio Broadcasting"),--(Sheet1!Q:Q="Quality")) Hope this helps. Pete On Mar 29, 1:08*pm, Sasikiran wrote: Dear, I am trying to work on a formula which gives the count within a date range with few specifications in some of the columns. The data is in Sheet1 and I want to insert the formula in Sheet2. From the raw data, I would like to get a formula which calculates the number of times the specified categories are present in these columns. The date is in m/d/yyyy h:mm format. Within the specified date range in column B, the formula should search for "Audio / Telephony / VOIP" in column O, "Audio Broadcasting" in column P, "Quality" in column Q and gives the count if all the criteria is matched. Please help =SUMPRODUCT((Sheet1!B:B=--"2/5/2010 *0:00:00 AM"), (Sheet1!B:B<=--"2/10/2010 *11:59:59 PM"), (Sheet1!O:O=--"Audio / Telephony / VOIP"), (Sheet1!P:P=--"Audio Broadcasting"), (Sheet1!Q:Q=--"Quality")) |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
The date range in the formula is not allowing me to get the desired figure
(Sheet1!B:B="2/5/2010 0:00:00 AM")*(Sheet1!B:B<="2/10/2010 11:59:59 PM") Please suggest "Eduardo" wrote: Hi, try =SUMPRODUCT((Sheet1!B:B="2/5/2010 0:00:00 AM")*(Sheet1!B:B<="2/10/2010 11:59:59 PM")*(Sheet1!O:O="Audio / Telephony / VOIP")*(Sheet1!P:P="Audio Broadcasting")*(Sheet1!Q:Q="Quality")) "Sasikiran" wrote: Dear, I am trying to work on a formula which gives the count within a date range with few specifications in some of the columns. The data is in Sheet1 and I want to insert the formula in Sheet2. From the raw data, I would like to get a formula which calculates the number of times the specified categories are present in these columns. The date is in m/d/yyyy h:mm format. Within the specified date range in column B, the formula should search for "Audio / Telephony / VOIP" in column O, "Audio Broadcasting" in column P, "Quality" in column Q and gives the count if all the criteria is matched. Please help =SUMPRODUCT((Sheet1!B:B=--"2/5/2010 0:00:00 AM"), (Sheet1!B:B<=--"2/10/2010 11:59:59 PM"), (Sheet1!O:O=--"Audio / Telephony / VOIP"), (Sheet1!P:P=--"Audio Broadcasting"), (Sheet1!Q:Q=--"Quality")) |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Hi,
could you please post an example of your data the results you are looking for thanks "Sasikiran" wrote: The date range in the formula is not allowing me to get the desired figure (Sheet1!B:B="2/5/2010 0:00:00 AM")*(Sheet1!B:B<="2/10/2010 11:59:59 PM") Please suggest "Eduardo" wrote: Hi, try =SUMPRODUCT((Sheet1!B:B="2/5/2010 0:00:00 AM")*(Sheet1!B:B<="2/10/2010 11:59:59 PM")*(Sheet1!O:O="Audio / Telephony / VOIP")*(Sheet1!P:P="Audio Broadcasting")*(Sheet1!Q:Q="Quality")) "Sasikiran" wrote: Dear, I am trying to work on a formula which gives the count within a date range with few specifications in some of the columns. The data is in Sheet1 and I want to insert the formula in Sheet2. From the raw data, I would like to get a formula which calculates the number of times the specified categories are present in these columns. The date is in m/d/yyyy h:mm format. Within the specified date range in column B, the formula should search for "Audio / Telephony / VOIP" in column O, "Audio Broadcasting" in column P, "Quality" in column Q and gives the count if all the criteria is matched. Please help =SUMPRODUCT((Sheet1!B:B=--"2/5/2010 0:00:00 AM"), (Sheet1!B:B<=--"2/10/2010 11:59:59 PM"), (Sheet1!O:O=--"Audio / Telephony / VOIP"), (Sheet1!P:P=--"Audio Broadcasting"), (Sheet1!Q:Q=--"Quality")) |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Try
=SUMPRODUCT(--(Sheet1!B2:B200=--"2010-02-05"),--(Sheet1!B2:B200<=--"2010-2--10"), --(Sheet1!O2:O200="Audio / Telephony / VOIP"),--(Sheet1!P2:P200="Audio Broadcasting"), Sheet1!Q2:Q200="Quality") -- HTH Bob "Sasikiran" wrote in message ... The date range in the formula is not allowing me to get the desired figure (Sheet1!B:B="2/5/2010 0:00:00 AM")*(Sheet1!B:B<="2/10/2010 11:59:59 PM") Please suggest "Eduardo" wrote: Hi, try =SUMPRODUCT((Sheet1!B:B="2/5/2010 0:00:00 AM")*(Sheet1!B:B<="2/10/2010 11:59:59 PM")*(Sheet1!O:O="Audio / Telephony / VOIP")*(Sheet1!P:P="Audio Broadcasting")*(Sheet1!Q:Q="Quality")) "Sasikiran" wrote: Dear, I am trying to work on a formula which gives the count within a date range with few specifications in some of the columns. The data is in Sheet1 and I want to insert the formula in Sheet2. From the raw data, I would like to get a formula which calculates the number of times the specified categories are present in these columns. The date is in m/d/yyyy h:mm format. Within the specified date range in column B, the formula should search for "Audio / Telephony / VOIP" in column O, "Audio Broadcasting" in column P, "Quality" in column Q and gives the count if all the criteria is matched. Please help =SUMPRODUCT((Sheet1!B:B=--"2/5/2010 0:00:00 AM"), (Sheet1!B:B<=--"2/10/2010 11:59:59 PM"), (Sheet1!O:O=--"Audio / Telephony / VOIP"), (Sheet1!P:P=--"Audio Broadcasting"), (Sheet1!Q:Q=--"Quality")) |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
Hi,
It's just for the date range the formula is not working. I tried all the possible ways but it's returning to either #N/A or 0. The rest of the formula which would look only for the specified text in the columns O, P & Q are working fine. Here is the example.. Create Date Category Type Item 2/1/2010 10:37 Audio Broadcasting Quality 2/1/2010 18:09 Audio Broadcasting Quality 2/2/2010 9:20 Audio Broadcasting Quality 2/2/2010 14:52 Audio Broadcasting Quality 2/2/2010 20:17 Audio Broadcasting Quality 2/3/2010 16:19 Audio Broadcasting Quality 2/3/2010 16:29 None Other Okay 2/5/2010 12:06 None Broadcasting Quality 2/5/2010 12:53 Audio Broadcasting Quality 2/7/2010 18:54 Audio Broadcasting Quality 2/8/2010 3:57 None Other Quality 2/8/2010 10:09 Audio Broadcasting Okay 2/10/2010 9:13 Audio Broadcasting Quality 2/10/2010 10:11 Audio Broadcasting Quality 2/10/2010 10:16 Audio Broadcasting Quality The create date is in the 2/7/2010 6:54:15 PM format.. Now the formula should pick the count which satifies all within the date range 2/5/2010 0:00 and 2/10/2010 23:59 in column A, with category as "Audio" in column B, Type as "Broadcasting" in column C, and Item as "Quality" in column D. Options tried for the date range: (Sheet1!B:B="5-Feb")*(Sheet1!B:B<="10-Feb")* (Sheet1!B2:B222="5-Feb")*(Sheet1!B2:B222<="10-Feb")* (Sheet1!B:B="2/5/2010 12:00:00 AM")*(Sheet1!B:B<="2/10/2010 11:59:59 PM")* (Sheet1!B2:B222="2/5/2010 12:00:00 AM")*(Sheet1!B2:B222<="2/10/2010 11:59:59 PM")* (Sheet1!B:B="2/5/2010 12:00 AM")*(Sheet1!B:B<="2/10/2010 11:59 PM")* (Sheet1!B2:B222="2/5/2010 12:00 AM")*(Sheet1!B2:B222<="2/10/2010 11:59 PM")* (Sheet1!B:B="2/5/2010 00:00")*(Sheet1!B:B<="2/10/2010 23:59")* (Sheet1!B2:B222="2/5/2010 00:00")*(Sheet1!B2:B222<="2/10/2010 23:59")* "Eduardo" wrote: Hi, could you please post an example of your data the results you are looking for thanks "Sasikiran" wrote: The date range in the formula is not allowing me to get the desired figure (Sheet1!B:B="2/5/2010 0:00:00 AM")*(Sheet1!B:B<="2/10/2010 11:59:59 PM") Please suggest "Eduardo" wrote: Hi, try =SUMPRODUCT((Sheet1!B:B="2/5/2010 0:00:00 AM")*(Sheet1!B:B<="2/10/2010 11:59:59 PM")*(Sheet1!O:O="Audio / Telephony / VOIP")*(Sheet1!P:P="Audio Broadcasting")*(Sheet1!Q:Q="Quality")) "Sasikiran" wrote: Dear, I am trying to work on a formula which gives the count within a date range with few specifications in some of the columns. The data is in Sheet1 and I want to insert the formula in Sheet2. From the raw data, I would like to get a formula which calculates the number of times the specified categories are present in these columns. The date is in m/d/yyyy h:mm format. Within the specified date range in column B, the formula should search for "Audio / Telephony / VOIP" in column O, "Audio Broadcasting" in column P, "Quality" in column Q and gives the count if all the criteria is matched. Please help =SUMPRODUCT((Sheet1!B:B=--"2/5/2010 0:00:00 AM"), (Sheet1!B:B<=--"2/10/2010 11:59:59 PM"), (Sheet1!O:O=--"Audio / Telephony / VOIP"), (Sheet1!P:P=--"Audio Broadcasting"), (Sheet1!Q:Q=--"Quality")) |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
SUMPRODUCT
I tried it again and I got it... Thank you :)
"Eduardo" wrote: Hi, try =SUMPRODUCT((Sheet1!B:B="2/5/2010 0:00:00 AM")*(Sheet1!B:B<="2/10/2010 11:59:59 PM")*(Sheet1!O:O="Audio / Telephony / VOIP")*(Sheet1!P:P="Audio Broadcasting")*(Sheet1!Q:Q="Quality")) "Sasikiran" wrote: Dear, I am trying to work on a formula which gives the count within a date range with few specifications in some of the columns. The data is in Sheet1 and I want to insert the formula in Sheet2. From the raw data, I would like to get a formula which calculates the number of times the specified categories are present in these columns. The date is in m/d/yyyy h:mm format. Within the specified date range in column B, the formula should search for "Audio / Telephony / VOIP" in column O, "Audio Broadcasting" in column P, "Quality" in column Q and gives the count if all the criteria is matched. Please help =SUMPRODUCT((Sheet1!B:B=--"2/5/2010 0:00:00 AM"), (Sheet1!B:B<=--"2/10/2010 11:59:59 PM"), (Sheet1!O:O=--"Audio / Telephony / VOIP"), (Sheet1!P:P=--"Audio Broadcasting"), (Sheet1!Q:Q=--"Quality")) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
SumProduct | Excel Discussion (Misc queries) | |||
Sumproduct with Condition OR Sumproduct with ADDRESS function - HE | Excel Discussion (Misc queries) | |||
Conditional SUMPRODUCT or SUMPRODUCT with Filters | Excel Worksheet Functions | |||
Sumproduct | Excel Worksheet Functions | |||
sumproduct? sumif(sumproduct)? | Excel Worksheet Functions |