Excel calculated field - case when return value

0
votes

I'm trying to create a calculated field in a pivot table where it returns the total only if the category is meat (in this example anyways)

enter image description here

My attempt:

=IF('Food Category'="Meat",Count,0)

but when I add the calculated field as a sum I just get 0

Is this even possible? I know I could have 2 count columns, 1 for meat and 1 for fruit but I'd like to avoid that.

I'm using Excel 2010

Thanks

2
excelpivot-tablecalculated-field
try replacing Count with 1, you haven't told it what to count. you just dropped an aggregate command into the formula. – DDuffy
=IF('Food Category'= "Meat",1,0), =SUM(IF('Food Category'= "Meat",1,0) ) return 0 and =IF('Food Category'= "Meat",SUM('Count'),0) gives an error – Bob Smith

2 Answers

0
votes

Unfortunately, it looks like this is not possible.

"See Here."

It appears In pivot table calculated fields, text values are seen as 0.

Best option would be to add a marker column. I.E. =if(A12 = "Meat",1,0) and set the calculated column to =if('Marker'=1,'Count',0)

0
votes

Try this:

=SUMIF(Food Category,"Meat",Count)

I am assuming Food Category and Count are named ranges for A13:A20 and C13:C20 respectively.

This should work even if Food Category and Count are column names.