Best formula to choose

Copper Contributor

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?) ) )