Hi, I have a big spreadsheet that links into many others.
I normally use a countif but it counts all of the data even when using a filter.
I only want it to only count the filtered data.
So how would I go about this?
This is my current countif:
=COUNTIF(Q1_Data,B4)
Q1_Data= A different worksheet (=Sheet1!$B$13:$AD$4717)
B4 = Text "Post Sales"
Thanks
See this in post#5
http://www.excelforum.com/excel-gene...ered-list.html
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
I've tried it with that post already, but I can't seem to get it working, I think it is because of the array of data I have and where it is a different sheet whereas that post is only on a single sheet.
Thanks
Ok. Give a try to this ARRAY formula(use a named range).
Formula:
Please Login or Register to view this content.
-- Array(CSE) { }, formulae are confirmed with Control+Shift+Enter.
Not just Enter.
Thanks, I gave it a go, changed my named range to my selected data (Q1_Data), made it into a array, and it counts the correct ones.
However, when I apply the filter, it still counts the values, I wanted it not to count when the filter was applied.
Thanks
What do you mean by that? Did you confirmed using Ctr+Shift+Enter the formula and you don't get the correct results?.. made it into a array
If yes, then we need a sample workbook for testing.
Yeah I used Ctr+Shift+Enter.Originally Posted by Fotis1991
Here is my sample worksheet.
Formula works if you change the named range to D3:V26, BUT the layout of your sheet is not as you described(validations list inside the range-many empty columns-merged cells. and so on.....)
[/I]Thanks, but I don't think it quite works.
There is a little adjustment in your worksheet.
The formula goes as follows:
However, when it gets to cell B14 which is "Holiday" the formula changes to this:
Please Login or Register to view this content.
This means that when I apply the filter, deselecting employee 3 which has all holiday selected, there is still data being read. This is the same trend as the "TBC" category.Please Login or Register to view this content.
EDIT: Oh I see it is because I have "Holiday" for the green section and for the drop down box. So it is counting it twice.
Last edited by Tom005; 06-06-2014 at 06:42 AM.
I modified a little the formula for the last 2 cells as i thought that original formula doesn't gives the correct results!
But you are the one that knows your project better.
Thanks again, ideally without the *2 and *4 it makes it a bit easier for me.
However I've now found a problem.
For each employee they have a AM activity and a PM activity. With the formula in your spreadsheet, it only counts the AM activities and not the PM activities.
If you change the filter to "Employee 2" and "Blanks" you will see that the data has "Other" for the AM activity and "Training" for the PM activity. Now looking at the "Statistics" you can see that it counted "Other" but it did not count "Training". Is there a way to count them data lists for each employee? I don't need the plain text that is entered such as "Test".
Thanks.
To be honest with you, perhaps i could do something BUT as i explained in my previous post i believe that using this layout for your sheet you'll have always problems. So i believe that is a loose of time to do anything else there.
Good luck
Oh right well thanks for your help. Don't really see what the formatting has to do with things but there we go.
Thanks again.
Sorry again. Needs too time to show you how to create a new worksheet and i don't have such.
As an idea, perhaps to take a look to the Commercial Sub forum of the side.
Maybe there with few points, you'll find an Expert to advice you and perhaps to create for you a workable solution.
Pls notice(for been clear) that i don't reply to that forum, so I have no interest in this.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks