Countif to exclude blanks
 

Hi folks,

I have looked in so many places to resolve this but cannot work it out so Ive come to the professionals.

Under the "what is should be excluding blanks", it is a count of how many numbers there are based on the site and week but when I ran a countif and several other functions based on what I have read online, it would still count 4 cells for week 1 instead of 2 cells (2 nonblank and 2 blank).

I was mainly using the countif function but couldnt get it to work.

Could somebody please provide some help?

I really hope this makes sense but please let me know if you require any more information.

Thanks
Pri

Countif to exclude blanks
 

I suggest using the SUMIF() function and a helper column, plus naming
the ranges as follows...

Column labeled "Week" (Col "C"):
Select all cells under label;
In the namebox type the sheetname wrapped in apostrophes,
then the exclamation character,
then "Week" as the range name (without quotes);
Example: 'sheet name'!Week
Press the 'Enter' key

Blank column to the right of "Week" (Col "E"):
Select the same number of cells as the range named "Week";
In the namebox type the sheetname wrapped in apostrophes,
then the exclamation character,
then "Count" as the range name (without quotes);
Example: 'sheet name'!Count
Press the 'Enter' key

While selected, start typing the following formula...
=IF(LEN($A1),1,0)
..and while holding down the 'Ctrl' key press 'Enter'

In the cells to the right of your list (Week1,Week2,Week3,Week4),
enter the following formulas...

=SUMIF(Week,"Week1",Count)
=SUMIF(Week,"Week2",Count)
=SUMIF(Week,"Week3",Count)
=SUMIF(Week,"Week4",Count)

Here's the results I get...

Week1 2
Week2 2
Week3 4
Week4 2

Note that you can hide the helper column if desired!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Hi Prets

Assuming your data including column headers are in A1:D14 and your criteria is in F2:F5. Two solutions using COUNTIFS if you have Excel 2007 or the SUMPRODUCT.

1: =COUNTIFS($C$2:$C$14,$F2,$A$2:$A$14,"0") and copy down.

2: =SUMPRODUCT(--($C$2:$C$14=$F2),--($A$2:$A$140))

Kevin

Hi Garry,

I used your method and it worked perfectly.
Thank you so much for taking the time to give me a detailed breakdown. It is much appreciated and will help throughout the rest of my project.

Kevin - I didn't see your post till just now but tried your method and again, it works perfectly. Thank you very much.

Many thanks
Pri