countif in pivot table field

[ajm]

folks,

I have a list of fuel purchase transactions and using a pivot table, need to show total quantity of fuel purchased by location, how many transactions per location, and identify how many of each sites transactions were for more than 200litresat a single refill. for example, Site 1 has had 65500 litres diesel bought over the year through 186 transactions. Of these, 24 were for a quantity greater than 200litres. So, in my pivot, the record for that site would look like :<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (ASAP Utilities for Excel - The essential add-in for Excel users. FREE excel tools and macros to save time. Download Excel tools) -->
<table border="1" bordercolor="#C0C0C0" bordercolordark="#FFFFFF" cellspacing="0" cellpadding="2">
<tr>
<td bgcolor="#DCE6F1" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Where</b></font></td>
<td bgcolor="#DCE6F1" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Sum of Quantity</b></font></td>
<td bgcolor="#DCE6F1" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>Count of Quantity</b></font></td>
<td bgcolor="#DCE6F1" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000"><b>>200</b></font></td>
</tr>
<tr>
<td bgcolor="#FFFFFF" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">Site 1</font></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">65,500</font></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">186</font></td>
<td bgcolor="#FFFFFF" align="right" valign="bottom" style="white-space: nowrap"><font face="Tahoma" size="2" color="#000000">8</font></td>
</tr>
</table>

Trouble is, I can't figure out how to do the count if greater than 200 Litres field. If I include it in the data table, it just gets counted the same as a transaction, and as a Calculated Field, I cannot get a Countif to work.

Any ideas?

 

[Robert Mika]

folks,

I have a list of fuel purchase transactions and using a pivot table, need to show total quantity of fuel purchased by location, how many transactions per location, and identify how many of each sites transactions were for more than 200litresat a single refill. for example, Site 1 has had 65500 litres diesel bought over the year through 186 transactions. Of these, 24 were for a quantity greater than 200litres. So, in my pivot, the record for that site would look like :<!-- Please do not remove this header -->
<!-- Table easily created from Excel with ASAP Utilities (ASAP Utilities for Excel - The essential add-in for Excel users. FREE excel tools and macros to save time. Download Excel tools) -->

Where	Sum of Quantity	Count of Quantity	>200
Site 1	65,500	186	8

<TBODY>

</TBODY>

Trouble is, I can't figure out how to do the count if greater than 200 Litres field. If I include it in the data table, it just gets counted the same as a transaction, and as a Calculated Field, I cannot get a Countif to work.

Any ideas?

Coudl you post your sample data?
(BTW you can always add another column colum to your data table
=IF(cell>200,1,0)
then add this column to your Pivot.

 

[ajm]

Thanks Robert. Unfortunately, I seem to be a bit thick in the head. I had used a helper column but with =IF(cell>200,"Y","N") which came up with the problem in my original post. changed it to =IF(cell>200,1,0), now, hey presto! no more problems. cheers, ajm

 

[Robert Mika]

Thanks Robert. Unfortunately, I seem to be a bit thick in the head. I had used a helper column but with =IF(cell>200,"Y","N") which came up with the problem in my original post. changed it to =IF(cell>200,1,0), now, hey presto! no more problems. cheers, ajm

You are welcome.