I am trying to alter a sheet which contains the following command:
=COUNTIF(Table1[PRS Review Date],"<="&TODAY()-21)
What this does show however is the total for ALL parts in the column. I have sorted the sheet to only show the parts applicable to me, thus this total is completely wrong.
How can I display the same data but for ONLY the parts which are being shown after sorting?
It sounds like you have a second criteria for the relevant parts, as well as the date criteria you are already using,
Use a COUNTIFS function with an extra criteria that identifies your parts.
Richard Buttrey
RIP - d. 06/10/2022
If any of the responses have helped then please consider rating them by clicking the small star iconbelow the post.
-Richard Buttrey
"second criteria for the relevant parts"
Maybe I'm not understanding what you mean but there is only one criteria:
Count of dates which are 21 days ago or older BUT I only want the count shown for the rows which are displayed and not for all the rows in the sheet.
I hope that clears it up a little more.
Thank you for your help
Hi,
I interpreted your indication that you had sorted the data to show just the rows you require as meaning there is a second criteria.
In other words what is determining which rows you show?
The request is not clear, at least to me.
You are saying you want to see the total for all parts but seemingly sorting the data to show just your parts.
Please upload the workbook and manually add the result you expect clearly identifying which are the result cells and which are original data. It's always easier if we can see requests in the context of their workbook.
Hello Richard,
I cannot upload as that will release too much information (596kb - company thing).
Let me try again:
We have two people using the sheet. Person A and Person B.
Person A is responsible for reviewing suppliers 1-20, Person B has 21-41. (they actually have names)
If I am Person A, I sort the column "Supplier" and select only my suppliers.
With the formula above, (Column PRS Review Date) it still shows in MY count how many parts both Person A AS WELL AS Person B have NOT updated in the past 21 days. I want this field to only show the count of parts which were not updated by me (Person A) which I have displayed. If I was to display ALL parts, the count would be correct, but once I sort by Person A parts only, the formula still shows the total for both Person A and Person B (even though data for Person B is NOT displayed).
Does that help clarify my mess?
We don't need to see zillions of original rows or irrelevant sheets. What we do need to see is your request in context. Experience tells us that these sorts of explanations are easily misinterpreted.
Just anonymise any sensitive data, we don't mind how many M Mouse, or D Ducks you use.
Created sample
Naturally my newbiness is showing since I cannot make the countif work in the 5 minutes I'm spending on this (sorry at work)
If I sort by buyer A, my total should be 2 as two dates are =<21 days before today. In my company sheet the answer would be 4 even though only buyer A is filtered.
I want the count displayed for ONLY the buyer selected (showing on screen).
Does this make it a bit clearer?
There is no simple function that will return the first value in a filtered list.
There is a formula solution using COUNTIFS() that requires a helper column with a complicated array formula. However since a helper column is needed it seems more appropriate to use a Pivot Table which is more elegant and more flexible.
See attached
Thank you, this 2/10 user is going down the vlookup rabbit hole how :D
A learning experience for sure!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks