Calculate how many of a value there are and return a percentage.

[Kylen]

Hi,

I need help creating a formula that calculates the following:

Part 1
Take the value in "B1" and look for it from Cell "A4" and to the end of the list. For each row where the value from "B1" is located, it must count what's in cell "C" on the same row. Cell C contains only two different values, either "Over" or "Under".

Values to be returned are what percentage of the rows where the value "B1" is located, contains the value "Over".

If there is no value in "B1", it should calculate the same as above, but not take into account what's in Cell A. It should only count how many "Over and" Under" there are in Cell C and what percentage is "Over".

	Name-2
Cell A3		Cell C3
Name-1		Over
Name-1		Under
Name-1		Under
Name-2		Over
Name-2		Over
Name-2		Under
Name-3		Under
Name-3		Over
Name-3		Under

Part 2
How do you make a chart that shows an arrow at the percentage calculated by the formula above?

Br
Kylen

 

[etaf]

Not sure I understand part 2

Part 1

Book1
	A	B	C	D
1	Cell A3	Name-2	Cell C3	Percent
2				67%
3
4	Name-1		Over
5	Name-1		Under
6	Name-1		Under
7	Name-2		Over
8	Name-2		Over
9	Name-2		Under
10	Name-3		Under
11	Name-3		Over
12	Name-3		Under
Sheet1

Cell Formulas
Range	Formula
D2	D2	=IF(B1="",COUNTIF(C4:C12,"over")/COUNTA(C4:C12),COUNTIFS(A4:A12,B1,C4:C12,"over")/COUNTIF(A4:A12,B1))

 

[Kylen]

Hi,

Thanks for your answer! It seems to work as expected.

Part 2
I want to create something similar to below that shows the percentage value from part -1. How to create one?

Br
Kylen

 

[etaf]

sorry not sure how to make those graphics