Hi all,
I am using a SUM/IF/FREQUENCY formula to count the number of bought items by store if certain conditions are met. Everything works fine until some of the items (style number/ CC) are removed in between. It doesn't seem to handle the blanks well for some reason, does anyone know why this messes it up and how to fix it?
What is the formula meant to be counting? Give details of the criteria in words.
Ali
Enthusiastic self-taught user of MS Excel who's always learning!
Don't forget to say "thank you" in your thread to anyone who has offered you help.
You can reward them by clicking on * Add Reputation below their user name on the left, if you wish.
Forum Rules (updated August 2023): please read them here.
Count if cell contains the following:
e.g.
x
xx
xxx
" x" (ignoring spaces)
5
8
10 (all numbers greater than 0)
but don't count above if row is "INACTIVE", "DROP" or column "CC" is blank and do not count duplicates in "CC" column twice if both have any of the above
Last edited by esbencito; 03-27-2018 at 01:25 AM.
Better if your can manually represent some expected result.
If I helped, Don't forget to add reputation (click on the little star ★ at bottom of this post)
Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
That's exactly what I did in my sample file! Green coloured row is the expected result, red the current formula...
Last edited by AliGW; 03-27-2018 at 01:44 AM. Reason: Unnecessary quotation removed.
This seems to get the results you want:
=COUNTIFS(J$13:J$170,"<>",$E$13:$E$170,"<>INACTIVE",$D$13:$D$170,"<>DROP")
Ali H column CC don't have duplicate value, U column result wouldn't correct
Nope... you are neglecting the duplicates as well as the fact that some cells might contain other inputs, such as "-" or "NA", also if there's no item number but an "x" it will still count it. It needs to be a SUMIFFREQUENCY formula not COUNTIFSOriginally Posted by AliGW
That's the only column that doesn't work. We just need to find a way round it. The problem with the OP's original formula is it's looking for unique sets of criteria, hence the low number. In other words, he's coming at it from the wrong angle.
Tim - please feel free to correct my mistake if you can.
Well, my start is a lot closer to the result you need than yours, but you obviously know better, so I'll leave you to it. Good luck!
close only in this sample file, in my actual data set, your formula would be off by about 20%!Well, my start is a lot closer to the result you need than yours, but you obviously know better, so I'll leave you to it. Good luck!
First result should be 60, how you suppose it 63 ? I have manually checked it ??
According my manual data check the result is vary from, First result is 60, 0, 43 and so on.
Try
K8Formula:
Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Then you need to provide more accurate sample data, don't you?close only in this sample file, in my actual data set, your formula would be off by about 20%!
Unfortunately that's not what I meant. I had just given you a few examples of the criteria it should look for. It should count ALL numbers above 0 not just 5, 8 and 10. Also it should count all "x" unless there's more than 3, so not "xxxx" and ignore spaces.According my manual data check the result is vary from, First result is 60, 0, 43 and so on.
Try
K8Formula:Please Login or Register to view this content.
...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.
Yup, my bad!Then you need to provide more accurate sample data, don't you?
J7Try this array formula and copy towards right
Please Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
Is there no way to simply adjust my original formula? I feel like things a becoming unnecessarily complex and long.
The only problem with the above is below part:Please Login or Register to view this content.
It somehow does not neglect blanks and counts wrong. If blank cells are filled in the "Attribute_CC" column, suddenly results are displayed correctly...Please Login or Register to view this content.
your formula can be modified like below
=SUM(IF(FREQUENCY(IF((ISNUMBER(MATCH(SUBSTITUTE(J$13:J$170," ",""),REPT("x",{1,2,3}),0))+(ISNUMBER(J$13:J$170)*(J$13:J$170>0)))*(Attribute_Drop<>"DROP")*(Attribute_Status<>"INACTIVE")*(Attribute_CC<>""),MATCH(Attribute_CC,Attribute_CC,0)),ROW(Attribute_CC)-12),1))
The root of the problem is the overly complex way you have of recording your data. A nice, neat, normalised layout would make all manner of analysis easier, but it would mean starting from scratch, which you probably don't want to do, or maybe can't as it is beyond your control.
Totally agree, unfortunately as you have already guessed, this is beyond my control...
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks