Using Countifs and Sumifs in Pivot Tables

bjurney

Active Member
Joined
Aug 24, 2009
Messages
320
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!
 

Some videos you may like

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,346
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

Active Member
Joined
Aug 24, 2009
Messages
320
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!:LOL:
 

Fazza

MrExcel MVP
Joined
May 17, 2006
Messages
9,346
The SQL requires no licence - it is normal Excel functionality.
 

PCRIDE

Well-known Member
Joined
Jan 20, 2008
Messages
890
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,346
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

Well-known Member
Joined
Jan 20, 2008
Messages
890
ah! missed that :)

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

bjurney

Active Member
Joined
Aug 24, 2009
Messages
320
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,346
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

MrExcel MVP
Joined
May 17, 2006
Messages
9,346
<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>
 

Watch MrExcel Video

Forum statistics

Threads
1,098,913
Messages
5,465,414
Members
406,426
Latest member
slari

This Week's Hot Topics

Top