# How do I create a chart with an or filter

Occasional Contributor

# How do I create a chart with an or filter

Hi,

I have this table

 Labels Blue Red White Green Blue;Red Y Y Red;White Y Y Green Y

I want to have a pie chart that will say the percentage I have from each color- how do I do that?

Instead of doing a sum if to get the total from each column

9 Replies

# Re: How do I create a chart with an or filter

I do not understand your chart. The colors go across, what do the combo-colors that go down represent? Also, based on the marking you have, what percent does each color have that would be represented by the pie chart?

# Re: How do I create a chart with an or filter

Should the second entry have a Y under the "White" header instead of the Blue header, making it Red;White?

# Re: How do I create a chart with an or filter

So assuming the 2nd Y under the Blue column should be under the White column, that would give us 5 total "Y's". Blue, White, Green, would each have 1, Red would have 2, Somewhere underneath the Green ROW (I will use row 5 in my example) create a formula to count the "Y" characters in the color columns.
Assuming "Labels" is in cell A1, in B5 write:
=countif(b2:d2,"Y") drag this formula across to the green column to copy to cell E5. Now select all four cells that do the counting (cells B5-E5) then go to the INSERT tab at the top of excel and find the Recommended Charts area, and select the pie chart. You can format it from there, including making the color in the pie match the entry. (Your charts may be elsewhere in the ribbon. Search for them)

# Re: How do I create a chart with an or filter

I will try to explain better- I need to do a count if on each color on a pivot table for each color and unify all these sums of colors. The total will be the amount of Y I have

# Re: How do I create a chart with an or filter

I do not understand the need for a pivot table for such a small amount of information. You originally requested a pie chart with percentages. My first answer met that requirement so please explain.

# Re: How do I create a chart with an or filter

Hi, eventually I want to have the relative amount per color (sum if per color) that has Y in this pie (count if)

 Amount/color Blue Red Green 5 Y Y 20 Y 6 Y sum 31 Count if 1 2 1 sum if blue 5 0 0 sum if red 0 25 0 sum if  green 0 0 6

# Re: How do I create a chart with an or filter

Ok, i get it. Is this chart the biggest it will be or is this the total size?

And to be sure, you are wanting formulas for the Sumif red, blue and green, in each column?

# Re: How do I create a chart with an or filter

Hi,

In my actual charts I have something like 5 categories (which are colors here) and 50 rows. The amount is a number that represents time, so my goal is to know how much time we invested per category aka color

Thanks,

Moran

# Re: How do I create a chart with an or filter

I placed your chart starting with cell A1.

In cell A5 put ="sum "&SUM(A2:A4)

"Sumif blue" is in A7. In B7 put =SUM(IF(B2="Y",\$A2,0)+IF(B3="Y",\$A3,0)+IF(B4="y",\$A4,0))

"Sumif red" is in A8.  In C8 put =SUM(IF(C3="Y",\$A3,0)+IF(C4="Y",\$A4,0)+IF(C5="y",\$A5,0))

"Sumif green" is in A9. in D9 put =SUM(IF(D4="Y",\$A4,0)+IF(D5="Y",\$A5,0)+IF(D6="y",\$A6,0))

All the other cells in the sumif range are just 0.

You can change the values in A2, A3, A4 to any number you wish and it will still add them correctly.