Need a formula to calculate stats for multiple classes

Hi-

This is a bit complicated, but hopefully I can explain my problem clearly:

I've generated a raw numerical output in a different program (ArcGIS, specifically), and I need to use it for some calculations. I've input it into excel, and it's organized as follows (see attached jpeg for a preview):

First column: "Zone" - these are the zones that I extracted the stats from, numbered about 0-580. There are multiple subsequent values for each zone, so the same zone value appears more than once.
Second column: "ID" - There is an individual ID for each of the values, ~0-2500 or so (lots of data).
Third column: "Class Value" - There are six possible value classes, numbered 0-5. Some of the zones have all six classes, and thus take up six rows, while some only have 5 of the 6, or 4/6, etc.
Fourth column: "Count" - The actual raw values that I want to work with.

What I want to do: For each zone, I want to get the percentage of Class Value "3" as compared to all the classes combined. For example, for the first Zone value (0), I'd add all the class counts together (650+27885+1341+5+47+1), and divide by the class 3 value (5), and multiply by 100 to get the percentage of that class within that zone. I want to do this for each of the 580 zones. The issue is, not every zone has all six classes present, and so if i were to create the formula from the first set of values (the red ones), and then drag it down to the bottom, the stats would be shifted incorrectly at the end, but I have way too many values to do it manually. I hope that makes sense.

So what I'm thinking is to somehow make the function work by grouping the zone values together? But I'm just not sure how to go about it. Any advice would be extremely appreciated. Thanks!

Cheers,
Emma

It would be very helpful if you could send the sample in excell... I think I can help you out..

That would be great! I included a small clip of my data. Thanks so much!

Cheers,

Emma

Thanks for your help, but could you explain this a bit? I'm not familiar with that function... I've plugged it into my spreadsheet, and it seems to be involving two different zone values, both 0 and 1? Thanks.

Cheers,
Emma

Thanks for your help, but could you explain this a bit? I'm not familiar with that function... I've plugged it into my spreadsheet, and it seems to be involving two different zone values, both 0 and 1? Thanks.

Cheers,
Emma

try this, to add zones just copy the rows down

This is awesome, thanks so much! Could you possibly explain it so I understand what exactly is being done? Also, I need to prevent the output values from rounding off... I need to know the results to at least 4 decimal points, if possible. Do you know how to change that issue? I apologize, I'm just not very fluent in excel.

Although I did it for you presenting 4 significant digits, It can be done by selecting the column, selecting the % format and then increasing the number of sig digits with the right pointing arrow marker (to the right of the format options).

OK. SUMIFS()

=IFERROR(SUMIFS($D:$D,$A:$A,$F14,$C:$C,G$2)/SUMIFS($D:$D,$A:$A,$F14),"") the formula that actually is in row 14, column G

The IFERROR() is to prevent a division by 0 if the sumnd zone values are 0.

The reasoning is that it will only sum the values of an especified range if ALL conditions are met.

The first parameter is the summing range, in your case column D

Next come the first condition: sum if the zone is equal to the one in column F , $A:$A,$F14

AND

second condition: sum if the case is equal to the case number in row 2 $C:$C,G$2)

The $ signs are there for copying and pasting purposes....

Regards,

RCM

Great, thanks so much! I really appreciate all your help.