Countif Help please

[xtwagon]

Hi,
Have a spreadsheet similar to below

12/9 ACT
14/9 SA
15/9 NSW
15/9
Vic
15/9 NSW
SA
16/9 SA

What my aim is to count the amount of cells that have an entry i
column B but only if there is an entry in column A. IE on 15/9, colum
B only has one entry, in this case the result would be 5

I have tried Using the Sumif and replaced "sum" with "count", this di
not work, I do not have a very good understanding of excel advance
formulas unfortunatly

Any help would be greatly appreciated.

Ma

 

[Bob Phillips]

Why would the result be 5?

I think this might start you

=SUMPRODUCT(--(A1:A100="15/9"),--(B1:B100<>""))

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

 

[xtwagon]

Thanks for that but I do not think that would do it.

The reason the sum will be 5 is that it is a total of of the entries in
column A that have a corresponding entry in Column B. Anything in
column A that has not had a date applied in Column B should not be
counted

I made a mistake when I posted the first post, will fix now.

 

[Bob Phillips]

How about

=COUNTIF(A:A,"<>")

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Guest]

This works for me:

=COUNTIF(OFFSET(A:A,,1),"<>")

**Note that it must be used in a cell outside the referenced range.

Regards,
GS

 

[daddylonglegs]

Surely

=SUMPRODUCT(--(A1:A100<>""),--(B1:B100<>""))

or even

=SUMPRODUCT(--(LEN(A1:A100)*LEN(B1:B100)>0))

 

[Guest]

Oops! ..I missed "only if there is an entry in column A."
Yeah, SUMPRODUCT for sure!

Thanks for the wake-up!!
GS