Hi All,
I hope someone can help with this.
I have attached a sample for convenience.
I am looking to be able to count in a table the word "Spare" where in matches the 2 criteria in a table range.
In the attached, I want to count the amount of times "JOHN" and "MARK" are "spare" in the Weeks where the numbers are in the table.
I have named the range where the data sits called "DataTable"
I hope this makes sense what I am trying to achieve
Thanks in advance
Spare Count1.xlsx
John
With an pivot table.
See the attached file.
Notice my main language is not English.
I appreciate it, if you reply on my solution.
If you are satisfied with the solution, please mark the question solved.
You can add reputation by clicking on the star * add reputation.
Please use this formula in J3
Run it across and down
=SUMPRODUCT(($A$2:$A$43=$I3)*(($B$2:$B$43=J$2)*(($D$2:$D$43="Spare"))))
If you like my answer please click on * Add Reputation
Don't forget to mark threads as "Solved" if your problem has been resolved
"Nothing is so firmly believed as what we least know."
--Michel de Montaigne
Thanks both for the responses - AlKey your solution best suits my needs as pivot tables dont update once you change the data if i'm correct?
Thank you for your feedback!
PivotTable can refresh on open under PivotTable options settings. It can work if new data is of the same size and range. Otherwise you would have to do it manually to adjust ranges or use VBA.
Please mark thread as "Solved" if your issue has been resolved. (Selecting Thread Tools-> Mark thread as Solved).
Spare Count1_Reply.xlsxjShaw82,
Please see attached -- I used the COUNTIFS formula.
HTH =)
the COUNTIFS formula seems to calculate quicker - thanks NU2vba!
Hi All, I have been requested to make a change the to spreadsheet that I am developing. Now have been asked to count the amount of "Spare" based on the month I have reattached a sample. The list in the sample would actually be a list for the whole year, i've just cut it down. I'm thinking the SUMPRODUCT would be best but not sure how to match "Nov" to the date range.
Thanks John
Spare Count2.xlsx
You A column missing names after row 64. Please add missing info.
My apologies.
Spare Count2.xlsx
Here a new formula:
=SUMPRODUCT(($A$2:$A$85=$I3)*((TEXT($C$2:$C$85,"MMM")=J$2)*(($D$2:$D$85="Spare"))))
Thanks AlKey - do you know if it may be possible to do this with COUNTIFS. The SUMPRODUCT is great but its slow calculating? I've tried messing with it but cant get it to work?
Enter this in J3 and copy down and across:
Formula:
Please Login or Register to view this content.
<---------If you like someone's answer, click the star to the left of one of their posts to give them a reputation point for that answer.Ron W
The problem here is that COUNTIFS would have to accept a formula as a criteria in order to mach month. About the speed of COUNTIFS vs SUMPRODUCT I really don't know. I believe that SUMPRODUCT in this case is the solution.
Seeing that you changed the criteria to Months, use a helper column E and enter this formula in E2 and copy down the length of the data:
Formula:Please Login or Register to view this content.
In J3 enter this formula and copy down and across:
Formula:Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks