Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF HELP
I am having major trouble with a countif formula
My Sheet looks like this A B C D 1 Call AM 136 2 Call AM 50 3 Net AM 38 4 Call CU 150 5 Net CU 32 6 Net CU 12 7 Call EF 145 I want another cell to do the following: COUNTIF B1:B600 = "AM", COUNT THE AMOUNT OF CELLS THAT ARE OVER 30. also figured to throw this in the mix too - IF A1:A600 = Call and B1:B600 is "Am", Count THE AMOUNT OF CELLS THAT ARE OVER 30. The problem with this is that I have two worksheets full of this data and i have about 80 "UNITS (i.e. AM, CU)" that I am working with... so manually counting them is kind of out of the question. Thanks again everyone -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200705/1 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF HELP
=sumproduct(--(b1:b600="AM"),--(c1:c60030))
and =sumproduct(--(a1:a600="Call"),--(b1:b600="AM"),--(c1:c60030)) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ========== You may want to add headers (in row 1) and add a formula in column D. Formula in D2: =if(c230,1,0) and drag down Then you could use that table to create a pivottable. Select the table (a1:D601) data|pivottable follow the wizard until you get to a step with a Layout button on it. Click that button. Drag the header for Column A to the Row field Drag the header for column B to the column field drag the header for column D to the Data field If the header for column D doesn't show "Sum of" Cancel this and go back to the original table and make sure your formula is correct in D2:D601. Then finish up this pivottable. "instereo911 via OfficeKB.com" wrote: I am having major trouble with a countif formula My Sheet looks like this A B C D 1 Call AM 136 2 Call AM 50 3 Net AM 38 4 Call CU 150 5 Net CU 32 6 Net CU 12 7 Call EF 145 I want another cell to do the following: COUNTIF B1:B600 = "AM", COUNT THE AMOUNT OF CELLS THAT ARE OVER 30. also figured to throw this in the mix too - IF A1:A600 = Call and B1:B600 is "Am", Count THE AMOUNT OF CELLS THAT ARE OVER 30. The problem with this is that I have two worksheets full of this data and i have about 80 "UNITS (i.e. AM, CU)" that I am working with... so manually counting them is kind of out of the question. Thanks again everyone -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200705/1 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
COUNTIF HELP
Dave-
Thank you very very much!!! This worked like a charm!! Dave Peterson wrote: =sumproduct(--(b1:b600="AM"),--(c1:c60030)) and =sumproduct(--(a1:a600="Call"),--(b1:b600="AM"),--(c1:c60030)) Adjust the ranges to match--but you can't use whole columns (except in xl2007). =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html ========== You may want to add headers (in row 1) and add a formula in column D. Formula in D2: =if(c230,1,0) and drag down Then you could use that table to create a pivottable. Select the table (a1:D601) data|pivottable follow the wizard until you get to a step with a Layout button on it. Click that button. Drag the header for Column A to the Row field Drag the header for column B to the column field drag the header for column D to the Data field If the header for column D doesn't show "Sum of" Cancel this and go back to the original table and make sure your formula is correct in D2:D601. Then finish up this pivottable. I am having major trouble with a countif formula [quoted text clipped - 24 lines] Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200705/1 -- Message posted via OfficeKB.com http://www.officekb.com/Uwe/Forums.a...excel/200705/1 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use a countif function according to two other countif fu. | Excel Worksheet Functions | |||
edit this =COUNTIF(A1:F16,"*1-2*")+COUNTIF(A1:F16,"*2-1*") | Excel Discussion (Misc queries) | |||
COUNTIF or not to COUNTIF on a range in another sheet | Excel Worksheet Functions | |||
Countif | Excel Worksheet Functions | |||
COUNTIF in one colum then COUNTIF in another...??? | Excel Worksheet Functions |