COUNTIF ?

D

DaveMoore

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
 
D

Don Guillett

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.
 
E

Eduardo

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
 
D

DaveMoore

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
 
T

T. Valko

(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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function Help - Forecast Date past due, adjacent cell is blank. Needto highlight the blank cell. 2
COUNTIFS ? 1
Return Each Last Non-blank Cell, Value 2 Column Offset, Many GAPS 1
Using COUNTIF with criteria in an adjacent column? 5
SUMIF with criteria "<>" & "=" 4
Count Blank Cells with Conditions 3
Sumif similar to Countif?? 4
Excel 2010 countifs for date range 3

Top