SUMPRODUCT

[Sasikiran]

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=--"Audio Broadcasting"), (Sheet1!Q:Q=--"Quality"))

 

[Eduardo]

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 /

 

[Pete_UK]

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="Audio Broadcasting"),--(Sheet1!Q:Q="Quality"))

Hope this helps.

Pete

 

[Sasikiran]

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]

Hi,
could you please post an example of your data the results you are looking
for thanks

 

[Bob Phillips]

Try

=SUMPRODUCT(--(Sheet1!B2:B200>=--"2010-02-05"),--(Sheet1!B2:B200<=--"2010-2--10"),
--(Sheet1!O2:O200="Audio / Telephony / VOIP"),--(Sheet1!P2200="Audio
Broadcasting"),
Sheet1!Q2:Q200="Quality")

 

[Sasikiran]

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")*

 

[Sasikiran]

I tried it again and I got it... Thank you

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 /