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

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