How do I create a chart with an or filter

%3CLINGO-SUB%20id%3D%22lingo-sub-1582304%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20chart%20with%20an%20or%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582304%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744550%22%20target%3D%22_blank%22%3E%40Moranh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20not%20understand%20your%20chart.%20The%20colors%20go%20across%2C%20what%20do%20the%20combo-colors%20that%20go%20down%20represent%3F%20Also%2C%20based%20on%20the%20marking%20you%20have%2C%20what%20percent%20does%20each%20color%20have%20that%20would%20be%20represented%20by%20the%20pie%20chart%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582309%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20chart%20with%20an%20or%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582309%22%20slang%3D%22en-US%22%3EShould%20the%20second%20entry%20have%20a%20Y%20under%20the%20%22White%22%20header%20instead%20of%20the%20Blue%20header%2C%20making%20it%20Red%3BWhite%3F%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1582357%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20chart%20with%20an%20or%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1582357%22%20slang%3D%22en-US%22%3ESo%20assuming%20the%202nd%20Y%20under%20the%20Blue%20column%20should%20be%20under%20the%20White%20column%2C%20that%20would%20give%20us%205%20total%20%22Y's%22.%20Blue%2C%20White%2C%20Green%2C%20would%20each%20have%201%2C%20Red%20would%20have%202%2C%20Somewhere%20underneath%20the%20Green%20ROW%20(I%20will%20use%20row%205%20in%20my%20example)%20create%20a%20formula%20to%20count%20the%20%22Y%22%20characters%20in%20the%20color%20columns.%3CBR%20%2F%3EAssuming%20%22Labels%22%20is%20in%20cell%20A1%2C%20in%20B5%20write%3A%3CBR%20%2F%3E%3Dcountif(b2%3Ad2%2C%22Y%22)%20drag%20this%20formula%20across%20to%20the%20green%20column%20to%20copy%20to%20cell%20E5.%20Now%20select%20all%20four%20cells%20that%20do%20the%20counting%20(cells%20B5-E5)%20then%20go%20to%20the%20INSERT%20tab%20at%20the%20top%20of%20excel%20and%20find%20the%20Recommended%20Charts%20area%2C%20and%20select%20the%20pie%20chart.%20You%20can%20format%20it%20from%20there%2C%20including%20making%20the%20color%20in%20the%20pie%20match%20the%20entry.%20(Your%20charts%20may%20be%20elsewhere%20in%20the%20ribbon.%20Search%20for%20them)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1580984%22%20slang%3D%22en-US%22%3EHow%20do%20I%20create%20a%20chart%20with%20an%20or%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1580984%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20have%20this%20table%26nbsp%3B%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CTABLE%20width%3D%22279%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2264%22%3ELabels%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EBlue%3C%2FTD%3E%3CTD%20width%3D%2263%22%3ERed%3C%2FTD%3E%3CTD%20width%3D%2246%22%3EWhite%3C%2FTD%3E%3CTD%20width%3D%2242%22%3EGreen%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EBlue%3BRed%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ERed%3BWhite%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3EGreen%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20want%20to%20have%20a%20pie%20chart%20that%20will%20say%20the%20percentage%20I%20have%20from%20each%20color-%20how%20do%20I%20do%20that%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EInstead%20of%20doing%20a%20sum%20if%20to%20get%20the%20total%20from%20each%20column%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThanks%20in%20advance!%26nbsp%3B%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1580984%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3ECharting%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1583866%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20chart%20with%20an%20or%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1583866%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F628423%22%20target%3D%22_blank%22%3E%40SqueakySneakers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20will%20try%20to%20explain%20better-%20I%20need%20to%20do%20a%20count%20if%20on%20each%20color%20on%20a%20pivot%20table%20for%20each%20color%20and%20unify%20all%20these%20sums%20of%20colors.%20The%20total%20will%20be%20the%20amount%20of%20Y%20I%20have%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584174%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20chart%20with%20an%20or%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584174%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744550%22%20target%3D%22_blank%22%3E%40Moranh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20do%20not%20understand%20the%20need%20for%20a%20pivot%20table%20for%20such%20a%20small%20amount%20of%20information.%20You%20originally%20requested%20a%20pie%20chart%20with%20percentages.%20My%20first%20answer%20met%20that%20requirement%20so%20please%20explain.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584210%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20chart%20with%20an%20or%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584210%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F628423%22%20target%3D%22_blank%22%3E%40SqueakySneakers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%20eventually%20I%20want%20to%20have%20the%20relative%20amount%20per%20color%20(sum%20if%20per%20color)%20that%20has%20Y%20in%20this%20pie%20(count%20if)%26nbsp%3B%3C%2FP%3E%3CTABLE%20width%3D%22287%22%3E%3CTBODY%3E%3CTR%3E%3CTD%20width%3D%2295%22%3EAmount%2Fcolor%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EBlue%3C%2FTD%3E%3CTD%20width%3D%2264%22%3ERed%3C%2FTD%3E%3CTD%20width%3D%2264%22%3EGreen%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E20%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E6%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3EY%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Esum%2031%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3ECount%20if%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3CTD%3E2%3C%2FTD%3E%3CTD%3E1%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Esum%20if%20blue%3C%2FTD%3E%3CTD%3E5%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Esum%20if%20red%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E25%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3Esum%20if%26nbsp%3B%20green%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E0%3C%2FTD%3E%3CTD%3E6%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584255%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20chart%20with%20an%20or%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584255%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744550%22%20target%3D%22_blank%22%3E%40Moranh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOk%2C%20i%20get%20it.%20Is%20this%20chart%20the%20biggest%20it%20will%20be%20or%20is%20this%20the%20total%20size%3F%3C%2FP%3E%3CP%3EAnd%20to%20be%20sure%2C%20you%20are%20wanting%20formulas%20for%20the%20Sumif%20red%2C%20blue%20and%20green%2C%20in%20each%20column%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584277%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20chart%20with%20an%20or%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584277%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F744550%22%20target%3D%22_blank%22%3E%40Moranh%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3BIn%20my%20actual%20charts%20I%20have%20something%20like%205%20categories%20(which%20are%20colors%20here)%20and%2050%20rows.%20The%20amount%20is%20a%20number%20that%20represents%20time%2C%20so%20my%20goal%20is%20to%20know%20how%20much%20time%20we%20invested%20per%20category%20aka%20color%3C%2FP%3E%3CP%3EThanks%2C%3C%2FP%3E%3CP%3EMoran%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1584296%22%20slang%3D%22en-US%22%3ERe%3A%20How%20do%20I%20create%20a%20chart%20with%20an%20or%20filter%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1584296%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F628423%22%20target%3D%22_blank%22%3E%40SqueakySneakers%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20placed%20your%20chart%20starting%20with%20cell%20A1.%3C%2FP%3E%3CP%3EIn%20cell%20A5%20put%20%3D%22sum%20%22%26amp%3BSUM(A2%3AA4)%3C%2FP%3E%3CP%3E%22Sumif%20blue%22%20is%20in%20A7.%20In%20B7%20put%20%3DSUM(IF(B2%3D%22Y%22%2C%24A2%2C0)%2BIF(B3%3D%22Y%22%2C%24A3%2C0)%2BIF(B4%3D%22y%22%2C%24A4%2C0))%3C%2FP%3E%3CP%3E%22Sumif%20red%22%20is%20in%20A8.%26nbsp%3B%20In%20C8%20put%20%3DSUM(IF(C3%3D%22Y%22%2C%24A3%2C0)%2BIF(C4%3D%22Y%22%2C%24A4%2C0)%2BIF(C5%3D%22y%22%2C%24A5%2C0))%3C%2FP%3E%3CP%3E%22Sumif%20green%22%20is%20in%20A9.%20in%20D9%20put%20%3DSUM(IF(D4%3D%22Y%22%2C%24A4%2C0)%2BIF(D5%3D%22Y%22%2C%24A5%2C0)%2BIF(D6%3D%22y%22%2C%24A6%2C0))%3C%2FP%3E%3CP%3EAll%20the%20other%20cells%20in%20the%20sumif%20range%20are%20just%200.%3C%2FP%3E%3CP%3EYou%20can%20change%20the%20values%20in%20A2%2C%20A3%2C%20A4%20to%20any%20number%20you%20wish%20and%20it%20will%20still%20add%20them%20correctly.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

Hi,

 

I have this table 

 
 
LabelsBlueRedWhiteGreen
Blue;RedYY  
Red;White YY
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 

 

Thanks in advance!  

9 Replies

@Moranh 

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?

Should the second entry have a Y under the "White" header instead of the Blue header, making it Red;White?
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)

@SqueakySneakers 

 

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

@Moranh 

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.

 

@SqueakySneakers 

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

Amount/colorBlueRedGreen 
5YY 
20 Y 
6  Y
sum 31   
Count if121
sum if blue500
sum if red0250
sum if  green006

@Moranh 

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?

@SqueakySneakers

 

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

 

@SqueakySneakers 

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.