Counting a Column when Three Conditions are true
 

I am having a difficult time finding anyone who can answer this question for
me.

A B C D
1 7/17/2006 06.123.456 224
2 7/17/2006 06.123.457
3 7/17/2006 06.123.458
4 7/17/2006 06.123.459 34
5 7/17/2006 06.123.460
6 7/17/2006 06.123.461
7 7/17/2006 06.123.462 32
8 7/17/2006 06.123.463 32
9 7/18/2006 06.123.464
10 7/19/2006 06.123.465
11 7/20/2006 06.123.466 32
12 7/21/2006 06.123.467
13 7/22/2006 06.123.468

Basically I need a formula to do the following: When Column "A" (Date)
equals a certain date (i.e.: 7/17/06), then look at corresponding columns in
that row where Column "C" (.06.123.456) and Column "D" (224). If they both
have a value (TRUE), then it has a value of (1), then sum all the instances
where it is true. In the example above, for 7/17/06 the answer would be 4.
The closestr thing I got wa the following function:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A205)*('Service Request
Log'!$C$4:$C$5000<"0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A205)*('Service Request Log'!$O$4:$O$5000)<0)

But it is summing the two columns ( "C" & "D" in the example) together so I
get a value of "12".

Thanks for any help someone might be able to give me.

Counting a Column when Three Conditions are true
 

Try this array formula:
=SUM(IF(A1:A10=F1,IF(C1:C10<"",IF(D1:D10<"",1,0) ,0),0))
then press Ctrl+Shift+Enter
Change the ranges as needed, F1 holds the search value

HTH

Die_Another_Day
jimswinder wrote:
I am having a difficult time finding anyone who can answer this question for
me.

A B C D
1 7/17/2006 06.123.456 224
2 7/17/2006 06.123.457
3 7/17/2006 06.123.458
4 7/17/2006 06.123.459 34
5 7/17/2006 06.123.460
6 7/17/2006 06.123.461
7 7/17/2006 06.123.462 32
8 7/17/2006 06.123.463 32
9 7/18/2006 06.123.464
10 7/19/2006 06.123.465
11 7/20/2006 06.123.466 32
12 7/21/2006 06.123.467
13 7/22/2006 06.123.468

Basically I need a formula to do the following: When Column "A" (Date)
equals a certain date (i.e.: 7/17/06), then look at corresponding columns in
that row where Column "C" (.06.123.456) and Column "D" (224). If they both
have a value (TRUE), then it has a value of (1), then sum all the instances
where it is true. In the example above, for 7/17/06 the answer would be 4.
The closestr thing I got wa the following function:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A205)*('Service Request
Log'!$C$4:$C$5000<"0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A205)*('Service Request Log'!$O$4:$O$5000)<0)

But it is summing the two columns ( "C" & "D" in the example) together so I
get a value of "12".

Thanks for any help someone might be able to give me.

Counting a Column when Three Conditions are true
 

Hi Jm,

I am sorry I did not understand your sample, on this case, there is just one
7/17/2006 - 06.123.456 - <0 and the formula should return 1 not 4.

I have used =sumproduct(--(c5:c17="7/17/2006")*(e5:e17=6123456)*(f5:f17<0))
and its run

please if it do not solve your problem let me know

hth
regards from Brazil
Marcelo

"jimswinder" escreveu:

I am having a difficult time finding anyone who can answer this question for
me.

A B C D
1 7/17/2006 06.123.456 224
2 7/17/2006 06.123.457
3 7/17/2006 06.123.458
4 7/17/2006 06.123.459 34
5 7/17/2006 06.123.460
6 7/17/2006 06.123.461
7 7/17/2006 06.123.462 32
8 7/17/2006 06.123.463 32
9 7/18/2006 06.123.464
10 7/19/2006 06.123.465
11 7/20/2006 06.123.466 32
12 7/21/2006 06.123.467
13 7/22/2006 06.123.468

Basically I need a formula to do the following: When Column "A" (Date)
equals a certain date (i.e.: 7/17/06), then look at corresponding columns in
that row where Column "C" (.06.123.456) and Column "D" (224). If they both
have a value (TRUE), then it has a value of (1), then sum all the instances
where it is true. In the example above, for 7/17/06 the answer would be 4.
The closestr thing I got wa the following function:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A205)*('Service Request
Log'!$C$4:$C$5000<"0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A205)*('Service Request Log'!$O$4:$O$5000)<0)

But it is summing the two columns ( "C" & "D" in the example) together so I
get a value of "12".

Thanks for any help someone might be able to give me.

Counting a Column when Three Conditions are true
 

HTH...you are a life saver!!!! IT worked in m y small example...now I will
try in my actuall spreadsheet.

Thanks!!!! :-)

"Die_Another_Day" wrote:

Try this array formula:
=SUM(IF(A1:A10=F1,IF(C1:C10<"",IF(D1:D10<"",1,0) ,0),0))
then press Ctrl+Shift+Enter
Change the ranges as needed, F1 holds the search value

HTH

Die_Another_Day
jimswinder wrote:
I am having a difficult time finding anyone who can answer this question for
me.

A B C D
1 7/17/2006 06.123.456 224
2 7/17/2006 06.123.457
3 7/17/2006 06.123.458
4 7/17/2006 06.123.459 34
5 7/17/2006 06.123.460
6 7/17/2006 06.123.461
7 7/17/2006 06.123.462 32
8 7/17/2006 06.123.463 32
9 7/18/2006 06.123.464
10 7/19/2006 06.123.465
11 7/20/2006 06.123.466 32
12 7/21/2006 06.123.467
13 7/22/2006 06.123.468

Basically I need a formula to do the following: When Column "A" (Date)
equals a certain date (i.e.: 7/17/06), then look at corresponding columns in
that row where Column "C" (.06.123.456) and Column "D" (224). If they both
have a value (TRUE), then it has a value of (1), then sum all the instances
where it is true. In the example above, for 7/17/06 the answer would be 4.
The closestr thing I got wa the following function:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A205)*('Service Request
Log'!$C$4:$C$5000<"0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A205)*('Service Request Log'!$O$4:$O$5000)<0)

But it is summing the two columns ( "C" & "D" in the example) together so I
get a value of "12".

Thanks for any help someone might be able to give me.

Counting a Column when Three Conditions are true
 

Try:

=SUMPRODUCT(--(B1:B13=G1),--(C1:C13<""),--(D1:D13<""))

g1=date (17/7/2006)

On your sample, answer is 4.

"Die_Another_Day" wrote:

Try this array formula:
=SUM(IF(A1:A10=F1,IF(C1:C10<"",IF(D1:D10<"",1,0) ,0),0))
then press Ctrl+Shift+Enter
Change the ranges as needed, F1 holds the search value

HTH

Die_Another_Day
jimswinder wrote:
I am having a difficult time finding anyone who can answer this question for
me.

A B C D
1 7/17/2006 06.123.456 224
2 7/17/2006 06.123.457
3 7/17/2006 06.123.458
4 7/17/2006 06.123.459 34
5 7/17/2006 06.123.460
6 7/17/2006 06.123.461
7 7/17/2006 06.123.462 32
8 7/17/2006 06.123.463 32
9 7/18/2006 06.123.464
10 7/19/2006 06.123.465
11 7/20/2006 06.123.466 32
12 7/21/2006 06.123.467
13 7/22/2006 06.123.468

Basically I need a formula to do the following: When Column "A" (Date)
equals a certain date (i.e.: 7/17/06), then look at corresponding columns in
that row where Column "C" (.06.123.456) and Column "D" (224). If they both
have a value (TRUE), then it has a value of (1), then sum all the instances
where it is true. In the example above, for 7/17/06 the answer would be 4.
The closestr thing I got wa the following function:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A205)*('Service Request
Log'!$C$4:$C$5000<"0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A205)*('Service Request Log'!$O$4:$O$5000)<0)

But it is summing the two columns ( "C" & "D" in the example) together so I
get a value of "12".

Thanks for any help someone might be able to give me.

Counting a Column when Three Conditions are true
 

Sorry Marcelo...you misunderstood what I needed. The only thing it had to
equal was the date...the other two columns just had to be a value greater
than zero.

"Marcelo" wrote:

Hi Jm,

I am sorry I did not understand your sample, on this case, there is just one
7/17/2006 - 06.123.456 - <0 and the formula should return 1 not 4.

I have used =sumproduct(--(c5:c17="7/17/2006")*(e5:e17=6123456)*(f5:f17<0))
and its run

please if it do not solve your problem let me know

hth
regards from Brazil
Marcelo

"jimswinder" escreveu:

I am having a difficult time finding anyone who can answer this question for
me.

A B C D
1 7/17/2006 06.123.456 224
2 7/17/2006 06.123.457
3 7/17/2006 06.123.458
4 7/17/2006 06.123.459 34
5 7/17/2006 06.123.460
6 7/17/2006 06.123.461
7 7/17/2006 06.123.462 32
8 7/17/2006 06.123.463 32
9 7/18/2006 06.123.464
10 7/19/2006 06.123.465
11 7/20/2006 06.123.466 32
12 7/21/2006 06.123.467
13 7/22/2006 06.123.468

Basically I need a formula to do the following: When Column "A" (Date)
equals a certain date (i.e.: 7/17/06), then look at corresponding columns in
that row where Column "C" (.06.123.456) and Column "D" (224). If they both
have a value (TRUE), then it has a value of (1), then sum all the instances
where it is true. In the example above, for 7/17/06 the answer would be 4.
The closestr thing I got wa the following function:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A205)*('Service Request
Log'!$C$4:$C$5000<"0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A205)*('Service Request Log'!$O$4:$O$5000)<0)

But it is summing the two columns ( "C" & "D" in the example) together so I
get a value of "12".

Thanks for any help someone might be able to give me.

Counting a Column when Three Conditions are true
 

Toppers:

YOur formula works also in my example..I will try and plug it into my real
spreadsheet and see if it also works there.

"Toppers" wrote:

Try:

=SUMPRODUCT(--(B1:B13=G1),--(C1:C13<""),--(D1:D13<""))

g1=date (17/7/2006)

On your sample, answer is 4.

"Die_Another_Day" wrote:

Try this array formula:
=SUM(IF(A1:A10=F1,IF(C1:C10<"",IF(D1:D10<"",1,0) ,0),0))
then press Ctrl+Shift+Enter
Change the ranges as needed, F1 holds the search value

HTH

Die_Another_Day
jimswinder wrote:
I am having a difficult time finding anyone who can answer this question for
me.

A B C D
1 7/17/2006 06.123.456 224
2 7/17/2006 06.123.457
3 7/17/2006 06.123.458
4 7/17/2006 06.123.459 34
5 7/17/2006 06.123.460
6 7/17/2006 06.123.461
7 7/17/2006 06.123.462 32
8 7/17/2006 06.123.463 32
9 7/18/2006 06.123.464
10 7/19/2006 06.123.465
11 7/20/2006 06.123.466 32
12 7/21/2006 06.123.467
13 7/22/2006 06.123.468

Basically I need a formula to do the following: When Column "A" (Date)
equals a certain date (i.e.: 7/17/06), then look at corresponding columns in
that row where Column "C" (.06.123.456) and Column "D" (224). If they both
have a value (TRUE), then it has a value of (1), then sum all the instances
where it is true. In the example above, for 7/17/06 the answer would be 4.
The closestr thing I got wa the following function:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A205)*('Service Request
Log'!$C$4:$C$5000<"0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A205)*('Service Request Log'!$O$4:$O$5000)<0)

But it is summing the two columns ( "C" & "D" in the example) together so I
get a value of "12".

Thanks for any help someone might be able to give me.

Counting a Column when Three Conditions are true
 

Thanks again to everyone that helped with this formula...all the formulas
that were sugggested work in my actual spreadsheet.

You guys who answer our pleas for help do a great job!!!!!!

Thanks again!!! :-)





"Toppers" wrote:

Try:

=SUMPRODUCT(--(B1:B13=G1),--(C1:C13<""),--(D1:D13<""))

g1=date (17/7/2006)

On your sample, answer is 4.

"Die_Another_Day" wrote:

Try this array formula:
=SUM(IF(A1:A10=F1,IF(C1:C10<"",IF(D1:D10<"",1,0) ,0),0))
then press Ctrl+Shift+Enter
Change the ranges as needed, F1 holds the search value

HTH

Die_Another_Day
jimswinder wrote:
I am having a difficult time finding anyone who can answer this question for
me.

A B C D
1 7/17/2006 06.123.456 224
2 7/17/2006 06.123.457
3 7/17/2006 06.123.458
4 7/17/2006 06.123.459 34
5 7/17/2006 06.123.460
6 7/17/2006 06.123.461
7 7/17/2006 06.123.462 32
8 7/17/2006 06.123.463 32
9 7/18/2006 06.123.464
10 7/19/2006 06.123.465
11 7/20/2006 06.123.466 32
12 7/21/2006 06.123.467
13 7/22/2006 06.123.468

Basically I need a formula to do the following: When Column "A" (Date)
equals a certain date (i.e.: 7/17/06), then look at corresponding columns in
that row where Column "C" (.06.123.456) and Column "D" (224). If they both
have a value (TRUE), then it has a value of (1), then sum all the instances
where it is true. In the example above, for 7/17/06 the answer would be 4.
The closestr thing I got wa the following function:

=SUMPRODUCT(--('Service Request Log'!$B$4:$B$5000=$A205)*('Service Request
Log'!$C$4:$C$5000<"0"))+SUMPRODUCT(--('Service Request
Log'!$B$4:$B$5000=$A205)*('Service Request Log'!$O$4:$O$5000)<0)

But it is summing the two columns ( "C" & "D" in the example) together so I
get a value of "12".

Thanks for any help someone might be able to give me.