Using Countifs and Sumifs in Pivot Tables

[bjurney]

I cant seem to find the answer to this anywhere and cant seemt o figure out how to do it. I am using Excel 2007 and was wondering if its possible to use a countif and/or sumif formulas in a calculated field.

I have a list with several diffrent groups with how long it took that group to accept a request assigned to them. I want to calculate, in the pivot table the number of times it took them more then 10 minutes to accept.

More or less =countif(A:A,">=10")

I figure I could always add a formula or use VBA to add a 1 in another column each time coulmn A is greater than 10 and have the pivot table to do a count based off that, but if it could be avoided, that would be great!

Hope my question makes sense to someone out there!

 

[Fazza]

hi. As you wrote, another field can be added to the source data. Without modifying the source data, AFAIK a normal calculated field can not do what you want and the only way is to do the calculation using SQL. Being SQL, the solution will be specific to your data. If you want help with that, please post some sample data and any necessary extra explanation of requirements. regards

 

[bjurney]

Thanks for the reply Fazza. I figured it may not be possible when Excel kept giving me an error. I would love to start using SQL, but my company has decided my line of work doesnt warrant a license, so I make due with what I have. Good ol' Excel!

 

[Fazza]

The SQL requires no licence - it is normal Excel functionality.

 

[PCRIDE]

This is actually quite simple. I do it all the time.

You can do this a few ways...

The easy way is just build a another column in your data set, with the formula you have below, with a slight modification.

=IF(A2>10,1,0)

Each row will have a 1 or 0 (Made or miss)

Then just create a pivot table, filtered on the misses.

If you are updating the data set, as long as you are pulling the same number of columns you won't over write the formulas.

If your data has the time already calculated, then go with the above.

 

[Fazza]

The question is looking for alternative approaches. cheers

I figure I could always add a formula or use VBA to add a 1 in another column each time coulmn A is greater than 10 and have the pivot table to do a count based off that, but if it could be avoided, that would be great!

 

[PCRIDE]

ah! missed that

Yea you would have to look at your data source and configure it there. (SQL, Access etc..)

 

[bjurney]

Thanks guys. PCRIDE that is what I had been doing actually and it does what I want ultimatly. I was just looking for a more efficient way to do it. Fazza, I am unfamiliar with the SQL suggestion. How would I go about doing that? I use 2007.

 

[Fazza]

Being SQL, the solution will be specific to your data. If you want help with that, please post some sample data and any necessary extra explanation of requirements.

As above, for specific help please post some sample data & any further explanation of requirements.

For previous examples suggest you search for old forum posts where I've used SQL in pivot tables as an alternative to pivot table calculated fields.

I'll search for some in a minute & post link/s.

 

[Fazza]

<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=559148</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=557553</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>
http://www.mrexcel.com/forum/showthread.php?t=529295
</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=516684</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=512312</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=449665</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=446928</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=440951</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=437647</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=434020</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=428942</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=405865</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=387110</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=386362</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=377726</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=373202</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=361454</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=353249</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=345697</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=343698</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=343562</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=339759</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 408pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=544 border=0 x:str><COLGROUP><COL style="WIDTH: 408pt; mso-width-source: userset; mso-width-alt: 19894" width=544><TBODY><TR style="HEIGHT: 12.75pt" height=17><TD style="BORDER-RIGHT: #d4d0c8; BORDER-TOP: #d4d0c8; BORDER-LEFT: #d4d0c8; WIDTH: 408pt; BORDER-BOTTOM: #d4d0c8; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" width=544 height=17>http://www.mrexcel.com/forum/showthread.php?t=317561</TD></TR></TBODY></TABLE>