Countif to exclude blanks

[Prets]

Hi folks,

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

Under the "what is should be excluding blanks", it is a count of ho
many numbers there are based on the site and week but when I ran
countif and several other functions based on what I have read online, i
would still count 4 cells for week 1 instead of 2 cells (2 nonblank an
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 an
more information.

Thanks
Pr

+-------------------------------------------------------------------
|Filename: Untitled.jpg
|Download: http://www.excelbanter.com/attachment.php?attachmentid=788
+-------------------------------------------------------------------

 

[GS]

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

 

[Kevin@Radstock]

Hi Prets

Assuming your data including column headers are in A114 and you
criteria is in F2:F5. Two solutions using COUNTIFS if you have Exce
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$14>0))

Kevin

Hi folks,

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

Under the "what is should be excluding blanks", it is a count of ho
many numbers there are based on the site and week but when I ran
countif and several other functions based on what I have read online, i
would still count 4 cells for week 1 instead of 2 cells (2 nonblank an
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 an
more information.

Thanks
Pr

+-------------------------------------------------------------------
+-------------------------------------------------------------------

 

[Prets]

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 an
again, it works perfectly. Thank you very much.

Many thanks
Pr

+-------------------------------------------------------------------
+-------------------------------------------------------------------