combing COUNTIF and COUNTA

[webknowhow]

I have a spreadsheet with A1:20 listing different codes (A,B,C,D,E) and B1:B20 listing more codes (E,F,G,H,I) with some blanks cells. I have a COUNTIF function for Col A to give me counts by code and a COUNTA function on col B to give me count on non blank cells...but what I am struggling with is combing these two function...i.e. I want to get a count of all code A in column A that does not have a empty cell in col B....I am going google eyed trying to work this out....has anyone got any ideas please.

 

[Domenic]

Try...

=SUMPRODUCT(--(A1:A20="A"),--(B1:B20<>""))

Hope this helps!

 

[webknowhow]

Domenic, that is fantastic...works a treat..thank you so much. I have to copy this formula across 50 other cells and each time it changes the A column to B column and the B col to C column...the only one that needs to change is the 2nd function as the A column is static....is there a way to alter the formula so even when copied the first part always relates to the A column?

 

[Domenic]

Try...

=SUMPRODUCT(--($A$1:$A$20="A"),--(B$1:B$20<>""))

Hope this helps!

 

[webknowhow]

thanks Domenic...just what I needed..you may have saved my eyesight