# Sum, Countifs, match Etc.

1. ## Sum, Countifs, match Etc.

I really didn't know how to title this as it seems like I need to nest a few functions together ha. I am needing to count the number of times each unique number is found in a column, based on a location (found in another column) based on a month and year (found in two additional columns)

I tried figuring this out on my own and didn't get far. below is what I have and it does not work to say the least. I know it doesn't include everything I'm asking for but as I said, I was trying to start somewhere.

``Please Login or Register  to view this content.``
The following are the columns of interest:

A = Location

H = Column to count unique numbers

M = Month

N = year

If I can get this knocked out, it will lead me to another question...but we can cross that bridge when we get there ha.

any and all help would be greatly appreciated.

thanks

BR

2. ## Re: Sum, Countifs, match Etc.

You could probably use a helper column to test for unique entries using COUNTIF(range,cel)=1)

3. ## Re: Sum, Countifs, match Etc.

after some more Googling, I was able to find this;

``Please Login or Register  to view this content.``
this has me half was there I think. it is telling me number of unique entries correctly.

Now I just need to count how many times each entry found. then narrow that down with countifs maybe to get it down to location, month, and year

4. ## Re: Sum, Countifs, match Etc.

Can you upload a small sample workbook, along with some sample answers?

5. ## Re: Sum, Countifs, match Etc.

Perhaps you can adapt the ranges in this for what you need?

 A B C D E 22 Name Data Duplicates CountUnique CountUniqueB 23 A 1 1 9 3 24 B 2 2 25 B 2 0 26 A 3 2 27 A 3 0 28 C 4 1 29 B 5 4 30 B 5 0 31 C 5 0 32 A 5 0 33 A 6 1 34 C 7 2 35 B 7 0 36 C 8 1 37 A 9 1

D23=SUMPRODUCT(--(FREQUENCY(B23:B37,B23:B37)>0))
E23=SUM(--(FREQUENCY(IF(A23:A37="B",B23:B37),B23:B37)>0))
E23 is an ARRAY formula, entered using CSE

6. ## Re: Sum, Countifs, match Etc.

Hopefully this will help. I tried the formulas you provided but couldn't get them to work. should the (--( be in there or is that a place holder for something I am supposed to enter?

Count Example.xlsx

thanks

7. ## Re: Sum, Countifs, match Etc.

Hi.

Can you just clarify some of your expected results?

You have a value of 1 for Alice/Oct-13, though I cannot see any records which match these criteria.

You have a value of 3 for Dickinson/Apr-14, though I only see 2 (unique) records which match these criteria.

You have a blank for Alice/Oct-14, though I can see 1 unique record which matches these criteria.

Regards

8. ## Re: Sum, Countifs, match Etc.

Originally Posted by XOR LX
Hi.

Can you just clarify some of your expected results?

You have a value of 1 for Alice/Oct-13, though I cannot see any records which match these criteria.

You have a value of 3 for Dickinson/Apr-14, though I only see 2 (unique) records which match these criteria.

You have a blank for Alice/Oct-14, though I can see 1 unique record which matches these criteria.

Regards
Your right. I had oct - 13 and 14 confused. as for Dickinson I must have over counted

9. ## Re: Sum, Countifs, match Etc.

You could use this array formula** in B2:

=SUM(IF(FREQUENCY(IF(Master!\$A\$2:\$A\$22=Results!\$A2,IF(Master!\$M\$2:\$M\$22=MONTH(Results!B\$1),IF(Master!\$N\$2:\$N\$22=YEAR(Results!B\$1),MATCH(Master!\$H\$2:\$H\$22,Master!\$H\$2:\$H\$22,0)))),ROW(Master!\$A\$2:\$A\$22)-MIN(ROW(Master!\$A\$2:\$A\$22))+1),1))

and copy down and to the right.

If you really don't want zeroes to appear you could mask them using either a Custom Cell Format or some Conditional Formatting.

Or, if you want to do it in the formula itself, perhaps:

=IFERROR(1/(1/(SUM(IF(FREQUENCY(IF(Master!\$A\$2:\$A\$22=Results!\$A2,IF(Master!\$M\$2:\$M\$22=MONTH(Results!B\$1),IF(Master!\$N\$2:\$N\$22=YEAR(Results!B\$1),MATCH(Master!\$H\$2:\$H\$22,Master!\$H\$2:\$H\$22,0)))),ROW(Master!\$A\$2:\$A\$22)-MIN(ROW(Master!\$A\$2:\$A\$22))+1),1)))),"")

which at least avoids a lengthy, resource-heavy repetition of the whole clause (i.e. IF(formula=0,"",formula), but you should be warned that this double-reciprocation trick can sometimes lead to rounding errors.

Probably best to go with one of the masking options if you really can't stand zeroes in there.

Regards

10. ## Re: Sum, Countifs, match Etc.

Nice, that works for me, thanks for the help.

on another note if you could help, now I need to find how many times a value is found in the well name column for a given month/year/district.

11. ## Re: Sum, Countifs, match Etc.

Nice, that works for me, thanks for the help.

on another note if you could help, now I need to find how many times a value is found in the well name column for a given month/year/district.

12. ## Re: Sum, Countifs, match Etc.

If you could re-post your attachment with some sort of results tabled included for this new task, then I'm sure I'd be able to help.

Regards

13. ## Re: Sum, Countifs, match Etc.

this should help.

Also, this being a sample or my actual data, the rows to be used go up to 10735 (including the header) when I tried changing the rows in the previous example to this number it returned a 0?

just thought I would throw that in there ha. thanks again for all your help

Count Example.xlsx

thanks
BD

14. ## Re: Sum, Countifs, match Etc.

Originally Posted by bdrilling33
this should help.
Sorry - is this not the same attachment as previously? Which part am I looking at in here now? Did you set up a table ready to house your new results?

Originally Posted by bdrilling33
Also, this being a sample or my actual data, the rows to be used go up to 10735 (including the header) when I tried changing the rows in the previous example to this number it returned a 0?
Did you remember to re-commit them as array formulas after making those changes?

Regards

15. ## Re: Sum, Countifs, match Etc.

well I thought I did, I must have made the changes and not save it or something. Nonetheless, we was able to get what I needed using a series of countifs and come cheater cells to achieve what I needed. Thanks again for all your help, it was much appreciated and will be used in the future.

thanks
Brice

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