List how many times an item appears in a category in another column

[caranpaima]

Hello everyone

After giving it a good thought for several days and looking around in this and other EXCEL forums, I finally gave up on finding out on my own on how to do the following operation (tried several countifs and sumproduct variations but could not get it to work):

Letīs say I have a list like the following two columns:

Block Species
1A Jaguar
1A Tucan
1B Iguana
1A Jaguar
1C Tucan
1D Iguana
1A Iguana
1D Iguana
1B Parrot
.... and another 15000 similar rows


Is there any way to calculate how many times did any of the species occur in the blocks? For instance, a result would be:

Species Number of Blocks
Jaguar 5
Parrot 7
Iguana 10

It is seemingly simple to do, and so far I am doing it with pivot table stuff, but would love it to have a formula for this. Stumped so far Any help would be welcome!

[jeffreybrown]

Hello & Welcome to the Board,

Please take a few minutes and read the Forum Rules about cross posts.

Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere.

It is preferred that you not cross-post at all, but if you do, please provide a link to the [http://www.mrexcel.com/forum/showthread.php?t=565197]cross-post[/url]

Have you looked at Countif?

[Marcol]

Hi caranpaima

Welcome to the forum ...

Perhaps I've had to much beer tonight, but if you only have one "Parrot", how can that return 7?
What am I missing?


You would do best to post a sample workbook showing your Sheet Layout and perhaps Before and After examples.

It should clearly illustrate your problem and not contain any sensitive data.


Cheers

From Forum FAQs

How do I attach a file to a post?

To attach a file to your post, you need to be using the main 'New Post' or 'New Thread' page and not 'Quick Reply'. To use the main 'New Post' page, click the 'Post Reply' button in the relevant thread.

On this page, below the message box, you will find a button labelled 'Manage Attachments'. Clicking this button will open a new window for uploading attachments. You can upload an attachment either from your computer or from another URL by using the appropriate box on this page. Alternatively you can click the Attachment Icon to open this page.

To upload a file from your computer, click the 'Browse' button and locate the file. To upload a file from another URL, enter the full URL for the file in the second box on this page. Once you have completed one of the boxes, click 'Upload'.

Once the upload is completed the file name will appear below the input boxes in this window. You can then close the window to return to the new post screen.

[caranpaima]

Thanks for answering!

I did not explain myself well enough... I oversimplified the example, but the idea is that I have two columns of data. One is a list of species, the other is a list of plots (blocks) where the particular species was found to occur. The result I need is:

- Two columns, one a list of all species, the second the number of plots each species occurred in.

I know (or thought I did) my way around countif and sumif, but this particular formula eludes me.

Please see the attachment.

[jeffreybrown]

Still not sure what you are looking for here.

What do you expect in the cell with ????

Also, why not just use =COUNTIF($B$2:$B$11152,F2) in cell H2. This would be much more efficient than the Sumproduct.

Have you considered a pivot table? Seems you could get your results rather quick.

[caranpaima]

Thanks for the quick reply.

What should be in the ??? cell and those below it are the results I'm looking for: The number of plots in which each species occurred. I'm attaching the file again, with the pivot table I currently use for the calculations (please see green shaded column), but it would be better if I could do it with a formula if possible, because I have to make many further calculations and it would be nice if I could limit the number of columns and sheets needed.

Also, I am aware that a countif would be more efficient, but was experimenting to see if I could get the same result with sumproduct and in the meantime, understand the formula well enough to get the other result I need.

Really annoyed that a seemingly simple problem escapes me...

[Marcol]

Hmm?

Best I can offer for now, got to go for a while, perhaps this will help.

Your sumproduct in Column "Total Number" seems to be the same as
In L2

Please Login or Register  to view this content.

I have created a list of "Unique IDPlots" in Column D
Then in E2

Please Login or Register  to view this content.

Drag/Fill Down.

Where $H$1 is the Drop-down list of species.

[EDIT]
Your profile states you are using 2003, but your workbook is 2007 or later, (and you have used COUNTIFS().)
If you don't need this to run in 2003 or earlier then this can replace the formula in E2

Please Login or Register  to view this content.

Select from the drop-down in H1 to get the statistics for each species in H2:H4

Hope this helps

[jeffreybrown]

Try....

=SUM(IF(FREQUENCY(IF(colB=$F2,IF(colA<>"",MATCH("~"&colA,colA&"",0))),ROW(colA)-ROW(A22)+1),1))

Named ranges:
colA =Sheet1!$A$2:$A$11152
colB =Sheet1!$B$2:$B$11152

IMPORTANT
This is an array formula
Enter the formula >> F2 >> CTRL + SHIFT + ENTER
If entered correctly, the formula will be enclosed in {brackets}
Do not enter the {brackets} manually

[Marcol]

@ Jeff

Neat one, just couldn't get my head 'round that one, maybe beer isn't the answer ....

I've added your formula to this workbook (Column R), along with a few ideas of my own that I was working with, hope you don't mind.

It runs like a farm tractor with a sump full of sawdust and tallow, but this can be improved.
Possibly with VBa arrays?

Select from the drop-down in J2. > put the kettle on > light a cig****** > watch your channel of choice > eventually see the results!

[jeffreybrown]

@ Marcol

Wow ...your not kidding...slow is an under-statement.

You can put lipstick on a pig, but in the end, it is still a pig.

I like the updates, but your right, improvements are desperately needed.

I don't have that kind of patience to live with the crawling nature of this workbook

[Marcol]

Hmm?

Perhaps a bit of rationalisation of the data might be the best way?

1/. Use Dynamic Named Ranges to keep the calculations to the absolute minimum. (See the names manager.)

2/. Sort the data by "Especie" then by "IDPlot", or vice versa.

3/. Use Column C as a helper
In C2

Please Login or Register  to view this content.

Drag/Fill Down

4/. Put the unique IDPlot values in Column E.
Copy Column A and paste to E > Data > Remove Duplicates.
Then sort this list.

5/. Use Columns F:G as a helpers
In F2

Please Login or Register  to view this content.

Drag/Fill Down

In G1

Please Login or Register  to view this content.

Drag/Fill Down

6/. Columns C;H can be hidden, I've used a Grouping Button. (+/-)

7/. In J5 (Blacked out, this will always return "")

Please Login or Register  to view this content.

In K6
=IF(J6="","",COUNTIFS(IDPlot,J6,Especie,$J$1))
Drag/Fill both Down at least 5 more rows than in the "Unique IDPlot" list.

The rest is just COUNTIFS() with or without OFFSET()

All this would be much tidier, in my opinion, and more reliable, with the IDPlots formatted as I have shown
(Compare the List Order in the two files)
If this isn't possible to do then all the IDs as you have them MUST be text.

Hope this helps, at least it's a tad faster, over 11k plus rows, than our earlier efforts!

[caranpaima]

Thank you!

Now that's a complicated formula!!!

And I was thinking I would get away with (a more complicated, but not so much) COUNTIFS or SUMPRODUCT...

And you're right. It can take five minutes to calculate once you open the file... slow! But it does the job.

[Marcol]

Hmm?

It can take five minutes to calculate once you open the file.

The workbooks in post #11? Surely not?

Other than the additional feature I added to list the IDPlot groups, the formulae are all simple Countifs.

It's the array functions that are dragging down the earlier solution, there are no arrays in the last posts.
(I suppose COUNTIFS() might be an array, but it is much more efficient than using SUMPRODUCT() when possible.)