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