Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting non blank cells for "sum(if" formula
I've been reading through previous threads and can't find the solution to my
problem. I need to count the number of cells that are "not blank" in column G after columns C and D meet the criteria ie: =SUM(IF('ALL WO List'!$C$1:$C$11716="FBR",1)*(IF('ALL WO List'!$D$1:$D$11716="MECH",1)*(IF('ALL WO List'!$G$1:$G$11716="not blank",1)))) Wild cards (?*) and "counta" don't seem to work....what am I missing? -- thanks, hostonthecoast |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
counting non blank cells for "sum(if" formula
I'd use =sumproduct() to avoid having to array enter the formula:
=SUMproduct(--('ALL WO List'!$C$1:$C$11716="FBR"), --('ALL WO List'!$D$1:$D$11716="MECH"), --('ALL WO List'!$G$1:$G$11716<"")) 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 If you're using xl2007, you may want to read about =countifs() in Help. hostonthecoast wrote: I've been reading through previous threads and can't find the solution to my problem. I need to count the number of cells that are "not blank" in column G after columns C and D meet the criteria ie: =SUM(IF('ALL WO List'!$C$1:$C$11716="FBR",1)*(IF('ALL WO List'!$D$1:$D$11716="MECH",1)*(IF('ALL WO List'!$G$1:$G$11716="not blank",1)))) Wild cards (?*) and "counta" don't seem to work....what am I missing? -- thanks, hostonthecoast -- Dave Peterson |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Formula for "counting" numbers - not cells! | Excel Worksheet Functions | |||
Formula for "counting" numbers - bot cells! | Excel Worksheet Functions | |||
In excel counting cells in a range which meet condition "Xand<X" | Excel Worksheet Functions | |||
Automatically add "0" to blank cells without a formula in the cel. | Excel Worksheet Functions | |||
Changing "returned" values from "0" to "blank" | Excel Worksheet Functions |