Home

Best formula to choose

%3CLINGO-SUB%20id%3D%22lingo-sub-803722%22%20slang%3D%22en-US%22%3EBest%20formula%20to%20choose%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-803722%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20wondering%20if%20there%20is%20a%20formula%20that%20can%20help%20me%20sort%20through%20my%20data.%3C%2FP%3E%3CP%3EI%20have%20a%20spreadsheet%20with%20dietary%20data%20on%20each%20food%20item%20a%20person%20has%20consumed.%20Essentially%20I%20want%20to%20break%20apart%20seperately%20the%20animal%20and%20plant%20protein%20sources%20for%20the%20total%20file.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EEach%20new%20line%20is%20a%20new%20food%20item.%20I%20have%20coded%20each%20food%20item%20with%20a%201%20for%20animal%20protein%20or%200%20for%20plant%20protein.%20I%20would%20like%20to%20add%20up%20the%20protein%20amount%20for%20each%20group%20seperately.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIs%20this%20possible%3F%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EKind%20regards%2C%3C%2FP%3E%3CP%3ESteph%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-803722%22%20slang%3D%22en-US%22%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-803804%22%20slang%3D%22en-US%22%3ERe%3A%20Best%20formula%20to%20choose%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-803804%22%20slang%3D%22en-US%22%3EFor%20animal%20protein%2C%20you%20can%20use%20a%20simple%20SUM%2C%20like%20this%3A%3CBR%20%2F%3E%3DSUM(A%3AA)%3CBR%20%2F%3EFor%20plant%20protein%2C%20you%20can%20use%20COUNTIF%2C%20like%20this%3A%3CBR%20%2F%3E%3DCOUNTIF(A%3AA%2C0)%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-804193%22%20slang%3D%22en-US%22%3ERe%3A%20Best%20formula%20to%20choose%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-804193%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F391930%22%20target%3D%22_blank%22%3E%40Steph_B%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20mention%20a%20'total'.%26nbsp%3B%20Is%20that%20the%20total%20quantity%20of%20animal%2Fplant%20protein%20or%20simply%20the%20total%20count%20of%20items%3F%26nbsp%3B%20To%20get%20the%20total%20quantities%20one%20could%20use%20any%20of%20the%20formulas%20shown%20below.%26nbsp%3B%20Mine%20look%20very%20different%20from%20Twifoo's%20simply%20because%20I%20choose%20not%20to%20use%20direct%20cell%20references%20of%20the%20type%20A1.%3C%2FP%3E%3CDIV%20class%3D%22mceNonEditable%20lia-copypaste-placeholder%22%3E%26nbsp%3B%3C%2FDIV%3E%3CTABLE%3E%3CTBODY%3E%3CTR%3E%3CTD%3ESource%3C%2FTD%3E%3CTD%3EFormula%3C%2FTD%3E%3CTD%3EType%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20size%3D%222%22%3EAnimal%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20size%3D%222%22%3E%3D%20SUMPRODUCT(%20Quantity%2C%20AnimalProtein%3F%20)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20size%3D%222%22%3ETraditional%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20size%3D%222%22%3EPlant%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20size%3D%222%22%3E%3D%20SUMPRODUCT(%20Quantity%2C%201%20-%20AnimalProtein%3F%20)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20size%3D%222%22%3EAnimal%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20size%3D%222%22%3E%3D%20SUMIFS(%20Quantity%2C%20AnimalProtein%3F%2C%201%20)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20size%3D%222%22%3ETabular%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20size%3D%222%22%3EPlant%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20size%3D%222%22%3E%3D%20SUMIFS(%20Quantity%2C%20AnimalProtein%3F%2C%200%20)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20size%3D%222%22%3EAnimal%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20size%3D%222%22%3E%3D%20SUM(%20IF(%20AnimalProtein%3F%2C%20Quantity%20)%20)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20size%3D%222%22%3ECSE%20Array%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20size%3D%222%22%3EPlant%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20size%3D%222%22%3E%3D%20SUM(%20IF(%20NOT(AnimalProtein%3F)%2C%20Quantity%20)%20)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20size%3D%222%22%3EAnimal%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20size%3D%222%22%3E%3D%20SUM(%20FILTER(%20Quantity%2C%20AnimalProtein%3F%20)%20)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20size%3D%222%22%3EDynamic%20Array%3C%2FFONT%3E%3C%2FTD%3E%3C%2FTR%3E%3CTR%3E%3CTD%3E%3CFONT%20size%3D%222%22%3EPlant%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%3CFONT%20size%3D%222%22%3E%3D%20SUM(%20FILTER(%20Quantity%2C%20NOT(AnimalProtein%3F)%20)%20)%3C%2FFONT%3E%3C%2FTD%3E%3CTD%3E%26nbsp%3B%3C%2FTD%3E%3C%2FTR%3E%3C%2FTBODY%3E%3C%2FTABLE%3E%3C%2FLINGO-BODY%3E
Steph_B
Occasional Visitor

Hi,

 

I am wondering if there is a formula that can help me sort through my data.

I have a spreadsheet with dietary data on each food item a person has consumed. Essentially I want to break apart seperately the animal and plant protein sources for the total file.

 

Each new line is a new food item. I have coded each food item with a 1 for animal protein or 0 for plant protein. I would like to add up the protein amount for each group seperately. 

 

Is this possible? 

 

Kind regards,

Steph

2 Replies
For animal protein, you can use a simple SUM, like this:
=SUM(A:A)
For plant protein, you can use COUNTIF, like this:
=COUNTIF(A:A,0)

@Steph_B 

You mention a 'total'.  Is that the total quantity of animal/plant protein or simply the total count of items?  To get the total quantities one could use any of the formulas shown below.  Mine look very different from Twifoo's simply because I choose not to use direct cell references of the type A1.

 
SourceFormulaType
Animal= SUMPRODUCT( Quantity, AnimalProtein? )Traditional
Plant= SUMPRODUCT( Quantity, 1 - AnimalProtein? ) 
Animal= SUMIFS( Quantity, AnimalProtein?, 1 )Tabular
Plant= SUMIFS( Quantity, AnimalProtein?, 0 ) 
Animal= SUM( IF( AnimalProtein?, Quantity ) )CSE Array
Plant= SUM( IF( NOT(AnimalProtein?), Quantity ) ) 
Animal= SUM( FILTER( Quantity, AnimalProtein? ) )Dynamic Array
Plant= SUM( FILTER( Quantity, NOT(AnimalProtein?) ) ) 
Related Conversations
Tabs and Dark Mode
cjc2112 in Discussions on
46 Replies
Extentions Synchronization
Deleted in Discussions on
3 Replies
Stable version of Edge insider browser
HotCakeX in Discussions on
35 Replies
flashing a white screen while open new tab
Deleted in Discussions on
14 Replies