Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting on two separate values
I'm trying to count the number of values, within a column over multiple rows,
that fall within a range. I can do that with the COUNTIF function as follows: =COUNTIF(Q6:Q53,"<31") However, now I want to extend that to break of the counts by month. I have a date in column C. If I create twelve row, each with a month/year, I want to use the month/year against column C. Something like below (which does not work). =IF(format(C6:C53,"MMYY")=format(A1, "MMYY"),COUNTIF(Q6:Q53,"<31"),0) where cell A1 contains a date like 1-Jul-2007. For cell B I would like a count where the month/year in C6:C53 is Jul-2007 AND the value in the range Q6:Q53 is less than 31. Thanks Leif |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting on two separate values
You could use SUMPRODUCT.
=SUMPRODUCT(--(MONTH(C6:C53)=7),--(YEAR(C6:C53)=2007),--(Q6:Q53<31)) If the month, year and less than amount are in a cell, say A1, B1 and C1: =SUMPRODUCT(--(MONTH(C6:C53)=A1),--(YEAR(C6:C53)=B1),--(Q6:Q53<C1)) HTH, Paul -- "Leif" wrote in message ... I'm trying to count the number of values, within a column over multiple rows, that fall within a range. I can do that with the COUNTIF function as follows: =COUNTIF(Q6:Q53,"<31") However, now I want to extend that to break of the counts by month. I have a date in column C. If I create twelve row, each with a month/year, I want to use the month/year against column C. Something like below (which does not work). =IF(format(C6:C53,"MMYY")=format(A1, "MMYY"),COUNTIF(Q6:Q53,"<31"),0) where cell A1 contains a date like 1-Jul-2007. For cell B I would like a count where the month/year in C6:C53 is Jul-2007 AND the value in the range Q6:Q53 is less than 31. Thanks Leif |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting on two separate values
=SUMPRODUCT(--(MONTH($C$6:$C$53)=MONTH($A$1)),--(YEAR($C$6:$C
$53)=YEAR($A$1)),--($Q$6:$Q$53<31)) Ken Johnson |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting on two separate values
Thanks Ken, that works. What is "--"?
"Ken Johnson" wrote: =SUMPRODUCT(--(MONTH($C$6:$C$53)=MONTH($A$1)),--(YEAR($C$6:$C $53)=YEAR($A$1)),--($Q$6:$Q$53<31)) Ken Johnson |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting on two separate values
Thanks Paul. Very close to what I needed. Since I have my month/year in a
single column the solution provided by Ken was right on. I need to check on "--", never heard of that before, except in the C programming language (autodecrement), but I don't think that what it means here. "PCLIVE" wrote: You could use SUMPRODUCT. =SUMPRODUCT(--(MONTH(C6:C53)=7),--(YEAR(C6:C53)=2007),--(Q6:Q53<31)) If the month, year and less than amount are in a cell, say A1, B1 and C1: =SUMPRODUCT(--(MONTH(C6:C53)=A1),--(YEAR(C6:C53)=B1),--(Q6:Q53<C1)) HTH, Paul -- "Leif" wrote in message ... I'm trying to count the number of values, within a column over multiple rows, that fall within a range. I can do that with the COUNTIF function as follows: =COUNTIF(Q6:Q53,"<31") However, now I want to extend that to break of the counts by month. I have a date in column C. If I create twelve row, each with a month/year, I want to use the month/year against column C. Something like below (which does not work). =IF(format(C6:C53,"MMYY")=format(A1, "MMYY"),COUNTIF(Q6:Q53,"<31"),0) where cell A1 contains a date like 1-Jul-2007. For cell B I would like a count where the month/year in C6:C53 is Jul-2007 AND the value in the range Q6:Q53 is less than 31. Thanks Leif |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Counting on two separate values
See http://www.xldynamic.com/source/xld.SUMPRODUCT.html for a detailed
explanation. -- --- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Leif" wrote in message ... Thanks Ken, that works. What is "--"? "Ken Johnson" wrote: =SUMPRODUCT(--(MONTH($C$6:$C$53)=MONTH($A$1)),--(YEAR($C$6:$C $53)=YEAR($A$1)),--($Q$6:$Q$53<31)) Ken Johnson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I chart two separate sets of x and corresponding y values . | Charts and Charting in Excel | |||
Counting based on values in two separate columns | Excel Worksheet Functions | |||
Counting values for variables in separate columns | Excel Discussion (Misc queries) | |||
counting rows with same values for multiple values | New Users to Excel | |||
Counting rows, then counting values. | Excel Discussion (Misc queries) |