Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 Quote:
|
#4
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
remove/delete/hide/exclude all (Blanks) in a pivot table macro | Excel Programming | |||
Pivot Table Macro, exclude possible blanks | Excel Programming | |||
copy range of cells with blanks then paste without blanks | Excel Programming | |||
copy range of cells with blanks then paste without blanks | Excel Worksheet Functions | |||
Paste Special Skip Blanks not skipping blanks, but overwriting... | Excel Discussion (Misc queries) |