For some reason the result is everyline in the column, I have tried "clear all" on the empty cells but to no joy.
Please Login or Register to view this content.
Does anyone have any idea why this is happening.
Side issue I have used the below code to try to counter this and it only produces results on values with a date, anything with writing (i.e. "Yes", "No") isn't included in the count.
Again any ideas why?
Please Login or Register to view this content.
can you post your example
of what, the result?
You may use:
=COUNTIF(Active!C:C,"<>")
Thanks Iz, that works perfectly,
Still having the problem of only counting dates with the other formula. I don't know why this is only counting dates
Please Login or Register to view this content.
That's standard COUNTIF behaviour if you use ">0" or similar it only counts numbers (dates are simply formatted numbers). If you wanted to count any text or number COUNTA is the normal option, e.g.
=COUNTA(Active!C:C)
Audere est facere
I tried this and it started counting all cells in the column again (65527) as soon as I placed it into the array?Please Login or Register to view this content.
Last edited by Sc0tt1e; 12-06-2013 at 09:56 AM.
why you don"t post a example
Becasue it is confidential data and I can't be putting it on the web.
Last edited by Sc0tt1e; 12-06-2013 at 10:18 AM.
I assumed you were using COUNTA with a single range as per your example. Can you explain what you are attempting to do with this formula?
=COUNTA(IF((Active!J$10:J$65536<>"")*(Active!K$10:K$65536=""),Active!J$10:J$65536))
By placing an example, the formulas will be translated, the note that my mother tongue is different
I am attempting to creat MI (Management Information).
If column J has a value (Yes or date) and Column K is empty then the unit of work is outstanding and the value in column J needs to be counted, If both cells have a value then it has moved from this stage and can be excluded from the O/s count by stage.
This then needs to be replicated for the 5 different stages so the o/s cases in stages 1-4 and the completed cases in Stage 5 (should) add to the total cases count already created by work allocation number.
Stage 1 <>"", Stage 2 "" = Count
Stage 2 <>"", Stage 3 "" = Count
Stage 3 <>"", Stage 4 "" = Count
Stage 4 <>"", Stage 5 "" = Count
Stage 5 <>"" = Completed cases = Count
Stage 1 = J
Stage 2 = K
Stage 3 = L
Stage 4 = M
Stage 5 = N
This way I can track all the cases within a specific work stream, find bottlenecks and re-allocate resources according to workload need.
To count rows where column J is populated but K isn't try using SUMPRODUCT like this
=SUMPRODUCT((Active!J$10:J$65536<>"")*(Active!K$10:K$65536=""))
If you don't really have 65527 lines of data then it would be better to use a smaller range
I don't know sumproducts, I haven't had the time to get my head around it and the spreadsheet is live and people will continue to us ethe spreadsheet so cell range will have to be whole columns.
SUMPRODUCT allows you to sum or count with multiple conditions (SUMIF and COUNTIF only allow one condition - SUMIFS and COUNTIFS are preferable for multiple conditions but only available in Excel 2007 or later versions).
It's up to you what ranges you use (You can't actually use the whole column in Excel 2003, SUMPRODUCT doesn't allow that but, starting at row 10 is OK), but it can impact on speed of calculation.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks