My spreadsheet has 8 columns (A:H).
Column 'A' is populated with a year (2005 to 2010) - not sorted.
Cells in Columns 'B' thru' 'H' are either blanks or numbers.

I want to COUNT the number of non-blank cells in column 'B' where the
adjacent cell in column 'A' = 2005.

Is there a formula that will do this for me?

Many thanks for any replies,
Regards,
Dave Moore

If desired, send your file to my address below. I will only look if:
1. You send a copy of this message on an inserted sheet
2. You give me the newsgroup and the subject line
3. You send a clear explanation of what you want
4. You send before/after examples and expected results.

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"DaveMoore" wrote in message
...
My spreadsheet has 8 columns (A:H).
Column 'A' is populated with a year (2005 to 2010) - not sorted.
Cells in Columns 'B' thru' 'H' are either blanks or numbers.

I want to COUNT the number of non-blank cells in column 'B' where the
adjacent cell in column 'A' = 2005.

Is there a formula that will do this for me?

Many thanks for any replies,
Regards,
Dave Moore

Try this...

=SUMPRODUCT(--(A2:A20=2005),--(B2:B20<""))

--
Biff
Microsoft Excel MVP


"DaveMoore" wrote in message
...
My spreadsheet has 8 columns (A:H).
Column 'A' is populated with a year (2005 to 2010) - not sorted.
Cells in Columns 'B' thru' 'H' are either blanks or numbers.

I want to COUNT the number of non-blank cells in column 'B' where the
adjacent cell in column 'A' = 2005.

Is there a formula that will do this for me?

Many thanks for any replies,
Regards,
Dave Moore

Hi,
In the cell where you want the total count enter

=SUMPRODUCT((A1:A12=2005)*B1:B12)

change range to fit your needs but remember the range has to be the same in
both side of the formula

"DaveMoore" wrote:

My spreadsheet has 8 columns (A:H).
Column 'A' is populated with a year (2005 to 2010) - not sorted.
Cells in Columns 'B' thru' 'H' are either blanks or numbers.

I want to COUNT the number of non-blank cells in column 'B' where the
adjacent cell in column 'A' = 2005.

Is there a formula that will do this for me?

Many thanks for any replies,
Regards,
Dave Moore
.

Oopps I missed something

=SUMPRODUCT((A1:A12=2005)*(B1:B12<""))

"Eduardo" wrote:

Hi,
In the cell where you want the total count enter

=SUMPRODUCT((A1:A12=2005)*B1:B12)

change range to fit your needs but remember the range has to be the same in
both side of the formula

"DaveMoore" wrote:

My spreadsheet has 8 columns (A:H).
Column 'A' is populated with a year (2005 to 2010) - not sorted.
Cells in Columns 'B' thru' 'H' are either blanks or numbers.

I want to COUNT the number of non-blank cells in column 'B' where the
adjacent cell in column 'A' = 2005.

Is there a formula that will do this for me?

Many thanks for any replies,
Regards,
Dave Moore
.

Thank you for this Biff, it works beautifully - but I don't understand
why?
Is there a webpage you can direct me to that will explain what these
(--) characters do in the formula?

Similarly, Eduardo's formula also worked. Thanks Eduardo but why does
this formula count the number of non-blank cells? I understand that
this part of the formula (A1:A12=2005) gives an answer of 1 which is
"TRUE" and is then multiplied by (B1:B12<"") - this is the part I do
not understand.
Can you help?


Many Thanks,
Dave Moore



On 15 Jan, 18:45, "T. Valko" wrote:
Try this...

=SUMPRODUCT(--(A2:A20=2005),--(B2:B20<""))

--
Biff
Microsoft Excel MVP

"DaveMoore" wrote in message

...



My spreadsheet has 8 columns (A:H).
Column 'A' is populated with a year (2005 to 2010) - not sorted.
Cells in Columns 'B' thru' 'H' are either blanks or numbers.

I want to COUNT the number of non-blank cells in column 'B' where the
adjacent cell in column 'A' = 2005.

Is there a formula that will do this for me?

Many thanks for any replies,
Regards,
Dave Moore- Hide quoted text -

- Show quoted text -

(B1:B12<"") - this is the part I do not understand.

< means not equal to (I think of it as meaning "is not")

"" means blank

So, put that together in plain English:

(B1:B12<"")

Check that the cells in the range B1:B12 "are not" blank. And the result is
an array of either TRUE or FALSE:

B1 "is not" blank = TRUE
B2 "is not" blank = FALSE
etc
etc
B12 "is not" blank = FALSE

See this for a comprehensive explanation on SUMPRODUCT:

http://xldynamic.com/source/xld.SUMPRODUCT.html

--
Biff
Microsoft Excel MVP


"DaveMoore" wrote in message
...
Thank you for this Biff, it works beautifully - but I don't understand
why?
Is there a webpage you can direct me to that will explain what these
(--) characters do in the formula?

Similarly, Eduardo's formula also worked. Thanks Eduardo but why does
this formula count the number of non-blank cells? I understand that
this part of the formula (A1:A12=2005) gives an answer of 1 which is
"TRUE" and is then multiplied by (B1:B12<"") - this is the part I do
not understand.
Can you help?


Many Thanks,
Dave Moore



On 15 Jan, 18:45, "T. Valko" wrote:
Try this...

=SUMPRODUCT(--(A2:A20=2005),--(B2:B20<""))

--
Biff
Microsoft Excel MVP

"DaveMoore" wrote in message

...



My spreadsheet has 8 columns (A:H).
Column 'A' is populated with a year (2005 to 2010) - not sorted.
Cells in Columns 'B' thru' 'H' are either blanks or numbers.

I want to COUNT the number of non-blank cells in column 'B' where the
adjacent cell in column 'A' = 2005.

Is there a formula that will do this for me?

Many thanks for any replies,
Regards,
Dave Moore- Hide quoted text -

- Show quoted text -