countif in pivot table field

ajm

Well-known Member
Joined
Feb 5, 2003
Messages
1,794
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?
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Robert Mika

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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) -->
WhereSum of QuantityCount of Quantity>200
Site 165,5001868


<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

Well-known Member
Joined
Feb 5, 2003
Messages
1,794
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

MrExcel MVP
Joined
Jun 29, 2009
Messages
7,256
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,126,910
Messages
5,621,593
Members
415,846
Latest member
mauryanil

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top