Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Worksheet Functions > Counting a Column when Three Conditions are true
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 32
Default 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.




Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 644
Default 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.

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 1,047
Default 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.




Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 32
Default 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.


Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 4,339
Default 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.


Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 32
Default 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.




Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 32
Default 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.


Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
  
Posts: 32
Default 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.


Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Column picked randomly with probability relative to number of entr Neil Goldwasser Excel Worksheet Functions 4 May 30th 06 08:55 AM
Conditional Format as a MACRO Gunjani Excel Worksheet Functions 3 March 29th 06 05:22 PM
Return SEARCHED Column Number of Numeric Label and Value Sam via OfficeKB.com Excel Worksheet Functions 23 January 30th 06 06:16 PM
creating a bar graph Johnfli Excel Discussion (Misc queries) 0 October 26th 05 08:16 PM
Add items in column 3 if column 1 and 2 conditions are met DB16 Excel Worksheet Functions 2 August 16th 05 02:00 PM


All times are GMT +1. The time now is 04:24 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement