|
Basic: Looking up Data in Range
This is probably basic stuff.
I presently use COUNTIF to count the number of instances of the letter "x" or "o" in a column: =COUNTIF(B:B,"x") I need to find totals of one condition in a column when another column has another condition. In the following data range, for example, I might need to find the number of times "x" appears in column B during a particular month, like February, or during a certain time of day. A B C January 4 x morning January 4 o afternoon January 12 o morning January 15 o morning January 15 x afternoon February 2 x morning February 2 x morning Ferbrary 4 o morning February 10 x morning I might also like to count, for example, the number of times "x" apppears in the morning in February. Please write out examples. My date format is MM-DD-YY. |
=SUMPRODUCT((TEXT(A1:A9,"mmm")="Jan")*(B1:B9="x"))
Count all instance of "x" where month = January. HTH Jason Atlanta, GA -----Original Message----- This is probably basic stuff. I presently use COUNTIF to count the number of instances of the letter "x" or "o" in a column: =COUNTIF(B:B,"x") I need to find totals of one condition in a column when another column has another condition. In the following data range, for example, I might need to find the number of times "x" appears in column B during a particular month, like February, or during a certain time of day. A B C January 4 x morning January 4 o afternoon January 12 o morning January 15 o morning January 15 x afternoon February 2 x morning February 2 x morning Ferbrary 4 o morning February 10 x morning I might also like to count, for example, the number of times "x" apppears in the morning in February. Please write out examples. My date format is MM-DD-YY. . |
=SUMPRODUCT(--(MONTH(A2:A200)=1),--(B2:B200="x"),--(C2:C200="morning"))
Regards, Peo Sjoblom "JonZ" wrote: This is probably basic stuff. I presently use COUNTIF to count the number of instances of the letter "x" or "o" in a column: =COUNTIF(B:B,"x") I need to find totals of one condition in a column when another column has another condition. In the following data range, for example, I might need to find the number of times "x" appears in column B during a particular month, like February, or during a certain time of day. A B C January 4 x morning January 4 o afternoon January 12 o morning January 15 o morning January 15 x afternoon February 2 x morning February 2 x morning Ferbrary 4 o morning February 10 x morning I might also like to count, for example, the number of times "x" apppears in the morning in February. Please write out examples. My date format is MM-DD-YY. |
According to my copy of EXCEL 2000, SUMPRODUCT is a math function and
returns sums of multiplications SUMPRODUCT Multiplies corresponding components in the given arrays, and returns the sum of those products. It would not work in doing what I want to do. You must have misunderstood my question. Jason Morin wrote: =SUMPRODUCT((TEXT(A1:A9,"mmm")="Jan")*(B1:B9="x")) Count all instance of "x" where month = January. HTH Jason Atlanta, GA -----Original Message----- This is probably basic stuff. I presently use COUNTIF to count the number of instances of the letter "x" or "o" in a column: =COUNTIF(B:B,"x") I need to find totals of one condition in a column when another column has another condition. In the following data range, for example, I might need to find the number of times "x" appears in column B during a particular month, like February, or during a certain time of day. A B C January 4 x morning January 4 o afternoon January 12 o morning January 15 o morning January 15 x afternoon February 2 x morning February 2 x morning Ferbrary 4 o morning February 10 x morning I might also like to count, for example, the number of times "x" apppears in the morning in February. Please write out examples. My date format is MM-DD-YY. . |
According to my copy of EXCEL 2000, SUMPRODUCT is a math function and returns
sums of multiplications SUMPRODUCT Multiplies corresponding components in the given arrays, and returns the sum of those products. It would not work in doing what I want to do. You must have misunderstood my question. Peo Sjoblom wrote: =SUMPRODUCT(--(MONTH(A2:A200)=1),--(B2:B200="x"),--(C2:C200="morning")) Regards, Peo Sjoblom "JonZ" wrote: This is probably basic stuff. I presently use COUNTIF to count the number of instances of the letter "x" or "o" in a column: =COUNTIF(B:B,"x") I need to find totals of one condition in a column when another column has another condition. In the following data range, for example, I might need to find the number of times "x" appears in column B during a particular month, like February, or during a certain time of day. A B C January 4 x morning January 4 o afternoon January 12 o morning January 15 o morning January 15 x afternoon February 2 x morning February 2 x morning Ferbrary 4 o morning February 10 x morning I might also like to count, for example, the number of times "x" apppears in the morning in February. Please write out examples. My date format is MM-DD-YY. |
No, I didn't misread your question. SUMPRODUCT is a
powerful alternative to array formulas as far as summing and counting when multiple criterias are needed. But you are right...it is simply a math function. To understand what I'm talking about, read: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Jason -----Original Message----- According to my copy of EXCEL 2000, SUMPRODUCT is a math function and returns sums of multiplications SUMPRODUCT Multiplies corresponding components in the given arrays, and returns the sum of those products. It would not work in doing what I want to do. You must have misunderstood my question. Jason Morin wrote: =SUMPRODUCT((TEXT(A1:A9,"mmm")="Jan")*(B1:B9="x")) Count all instance of "x" where month = January. HTH Jason Atlanta, GA -----Original Message----- This is probably basic stuff. I presently use COUNTIF to count the number of instances of the letter "x" or "o" in a column: =COUNTIF(B:B,"x") I need to find totals of one condition in a column when another column has another condition. In the following data range, for example, I might need to find the number of times "x" appears in column B during a particular month, like February, or during a certain time of day. A B C January 4 x morning January 4 o afternoon January 12 o morning January 15 o morning January 15 x afternoon February 2 x morning February 2 x morning Ferbrary 4 o morning February 10 x morning I might also like to count, for example, the number of times "x" apppears in the morning in February. Please write out examples. My date format is MM-DD-YY. . |
Just wondering if you tried the suggestions before posting back that it wouldn't
work??? JonZ wrote: According to my copy of EXCEL 2000, SUMPRODUCT is a math function and returns sums of multiplications SUMPRODUCT Multiplies corresponding components in the given arrays, and returns the sum of those products. It would not work in doing what I want to do. You must have misunderstood my question. Peo Sjoblom wrote: =SUMPRODUCT(--(MONTH(A2:A200)=1),--(B2:B200="x"),--(C2:C200="morning")) Regards, Peo Sjoblom "JonZ" wrote: This is probably basic stuff. I presently use COUNTIF to count the number of instances of the letter "x" or "o" in a column: =COUNTIF(B:B,"x") I need to find totals of one condition in a column when another column has another condition. In the following data range, for example, I might need to find the number of times "x" appears in column B during a particular month, like February, or during a certain time of day. A B C January 4 x morning January 4 o afternoon January 12 o morning January 15 o morning January 15 x afternoon February 2 x morning February 2 x morning Ferbrary 4 o morning February 10 x morning I might also like to count, for example, the number of times "x" apppears in the morning in February. Please write out examples. My date format is MM-DD-YY. -- Dave Peterson |
So, you post a question, you get 2 similar answers, whereupon you disregard
both answers without trying the solutions, pasting from excel help? Amazing! Here's some info on SUMPRODUCT http://www.xldynamic.com/source/xld.SUMPRODUCT.html -- Regards, Peo Sjoblom (No private emails please, for everyone's benefit keep the discussion in the newsgroup/forum) "JonZ" wrote in message ... According to my copy of EXCEL 2000, SUMPRODUCT is a math function and returns sums of multiplications SUMPRODUCT Multiplies corresponding components in the given arrays, and returns the sum of those products. It would not work in doing what I want to do. You must have misunderstood my question. Peo Sjoblom wrote: =SUMPRODUCT(--(MONTH(A2:A200)=1),--(B2:B200="x"),--(C2:C200="morning")) Regards, Peo Sjoblom "JonZ" wrote: This is probably basic stuff. I presently use COUNTIF to count the number of instances of the letter "x" or "o" in a column: =COUNTIF(B:B,"x") I need to find totals of one condition in a column when another column has another condition. In the following data range, for example, I might need to find the number of times "x" appears in column B during a particular month, like February, or during a certain time of day. A B C January 4 x morning January 4 o afternoon January 12 o morning January 15 o morning January 15 x afternoon February 2 x morning February 2 x morning Ferbrary 4 o morning February 10 x morning I might also like to count, for example, the number of times "x" apppears in the morning in February. Please write out examples. My date format is MM-DD-YY. |
That explains it.
I need to figure out how to extract the month from the date field. My date field contains a day, month and year. When I click on a date field the format is: 1/12/2004. Jason Morin wrote: No, I didn't misread your question. SUMPRODUCT is a powerful alternative to array formulas as far as summing and counting when multiple criterias are needed. But you are right...it is simply a math function. To understand what I'm talking about, read: http://www.xldynamic.com/source/xld.SUMPRODUCT.html Jason -----Original Message----- According to my copy of EXCEL 2000, SUMPRODUCT is a math function and returns sums of multiplications SUMPRODUCT Multiplies corresponding components in the given arrays, and returns the sum of those products. It would not work in doing what I want to do. You must have misunderstood my question. Jason Morin wrote: =SUMPRODUCT((TEXT(A1:A9,"mmm")="Jan")*(B1:B9="x")) Count all instance of "x" where month = January. HTH Jason Atlanta, GA -----Original Message----- This is probably basic stuff. I presently use COUNTIF to count the number of instances of the letter "x" or "o" in a column: =COUNTIF(B:B,"x") I need to find totals of one condition in a column when another column has another condition. In the following data range, for example, I might need to find the number of times "x" appears in column B during a particular month, like February, or during a certain time of day. A B C January 4 x morning January 4 o afternoon January 12 o morning January 15 o morning January 15 x afternoon February 2 x morning February 2 x morning Ferbrary 4 o morning February 10 x morning I might also like to count, for example, the number of times "x" apppears in the morning in February. Please write out examples. My date format is MM-DD-YY. . |
| All times are GMT +1. The time now is 03:51 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com