SUM/IF/FREQUENCY with blanks and duplicates

1. SUM/IF/FREQUENCY with blanks and duplicates

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?

2. Re: SUM/IF/FREQUENCY with blanks and duplicates

What is the formula meant to be counting? Give details of the criteria in words.

3. Re: SUM/IF/FREQUENCY with blanks and duplicates

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

4. Re: SUM/IF/FREQUENCY with blanks and duplicates

Better if your can manually represent some expected result.

5. Re: SUM/IF/FREQUENCY with blanks and duplicates

That's exactly what I did in my sample file! Green coloured row is the expected result, red the current formula...

6. Re: SUM/IF/FREQUENCY with blanks and duplicates

This seems to get the results you want:

=COUNTIFS(J\$13:J\$170,"<>",\$E\$13:\$E\$170,"<>INACTIVE",\$D\$13:\$D\$170,"<>DROP")

7. Re: SUM/IF/FREQUENCY with blanks and duplicates

Ali H column CC don't have duplicate value, U column result wouldn't correct

8. Re: SUM/IF/FREQUENCY with blanks and duplicates

Originally Posted by AliGW
This seems to get the results you want:

=COUNTIFS(J\$13:J\$170,"<>",\$E\$13:\$E\$170,"<>INACTIVE",\$D\$13:\$D\$170,"<>DROP")
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 COUNTIFS

9. Re: SUM/IF/FREQUENCY with blanks and duplicates

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.

10. Re: SUM/IF/FREQUENCY with blanks and duplicates

Originally Posted by esbencito
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 COUNTIFS
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!

11. Re: SUM/IF/FREQUENCY with blanks and duplicates

Originally Posted by AliGW
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%!

12. Re: SUM/IF/FREQUENCY with blanks and duplicates

First result should be 60, how you suppose it 63 ? I have manually checked it ??

13. Re: SUM/IF/FREQUENCY with blanks and duplicates

According my manual data check the result is vary from, First result is 60, 0, 43 and so on.

Try
K8
Formula:
`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.

14. Re: SUM/IF/FREQUENCY with blanks and duplicates

Originally Posted by esbencito
close only in this sample file, in my actual data set, your formula would be off by about 20%!
Then you need to provide more accurate sample data, don't you?

15. Re: SUM/IF/FREQUENCY with blanks and duplicates

Originally Posted by shukla.ankur281190
According my manual data check the result is vary from, First result is 60, 0, 43 and so on.

Try
K8
Formula:
`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.
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.

16. Re: SUM/IF/FREQUENCY with blanks and duplicates

Originally Posted by AliGW
Then you need to provide more accurate sample data, don't you?

17. Re: SUM/IF/FREQUENCY with blanks and duplicates

J7
``Please Login or Register  to view this content.``
Try this array formula and copy towards right

18. Re: SUM/IF/FREQUENCY with blanks and duplicates

Is there no way to simply adjust my original formula? I feel like things a becoming unnecessarily complex and long.

``Please Login or Register  to view this content.``
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...

19. Re: SUM/IF/FREQUENCY with blanks and duplicates

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))

20. Re: SUM/IF/FREQUENCY with blanks and duplicates

Originally Posted by esbencito
Is there no way to simply adjust my original formula? I feel like things a becoming unnecessarily complex and long.
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.

21. Re: SUM/IF/FREQUENCY with blanks and duplicates

Originally Posted by AliGW
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...

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1