count items conditionally ?

**Neutrino Two** · 05-27-2012, 10:55 PM

Is this even possible.
Example:

column A column B

B200 custname
B201 custname
C202 custname
C203
A204 custname
A205 custname

I can add up All items for hire by their code number,
I can add up All customers by counta Column b
I can add up all available code# available by countblank (Column b)
( If blank in column B - that item is available)

But how could I count all available C items ( we have A.B.C and D)
as in the above example it's return 1 available item in the "C" range.
Is it possible to Count an item only if there's a "blank" alongside in column B?
that way I could I could add up the # of C available ( or A or b or D )
Hope I'v expained that clearly ~
Many thanks.

**MarvinP** · 05-27-2012, 11:16 PM

Hi Neutrino Two,

If you had Excel 2007 or 2010 you could use the CountIFS() function and test the blank and the first letter being C. See the attached that I created with 2010 and saved as an .xlsx so I don't think you will be able to use Countifs with your 2003 version.

**benishiryo** · 05-27-2012, 11:18 PM

hi Neutrino Two, maybe u can try:

=SUMPRODUCT((LEFT($A$1:$A$6,1)="C")*($B$1:$B$6<>""))

let me know if this is what u need

**Cutter** · 05-28-2012, 09:19 AM

@benishiryo
I think the OP actually wants:

=SUMPRODUCT((LEFT($A$1:$A$6,1)="C")*($B$1:$B$6=""))

based on:

Is it possible to Count an item only if there's a "blank" alongside in column B?

**MarvinP** · 05-28-2012, 11:22 AM

I agree with Cutter, as I normally do. I want the OP to upgrade to 2010 Excel so s/he gets a better set of functions to work with.

**Neutrino Two** · 05-28-2012, 04:36 PM

=SUMPRODUCT((LEFT($A$1:$A$6,1)="C")*($B$1:$B$6=""))
is valid (no err) but is returning a value of 0
~?~
and yep.. as soon as poss I'd like ot upgrade to the latest
version... that'll be up to the boss!

but we are overdue
for a Pc replacement with a new faster one, this one's kinda old!
So here's hoping when that happens, we also upgrade the software.

anyway, Many Thanks, I'll need to do some understanding of the
above Function and see what it's returning 0 .