Maybe Countif? Very new to excel

[tym623]

I'm using Excel 2003 with Windows XP. I'm attempting to create VERY basic, sorted stats for a call center.

Data is captured into an excel sheet with calls sorted by different categories depending how a person logged a particular call.

For example, Caller A logs a call by selecting a Category 1, Cat. 2, 3, 4, etc. Sometimes a category 3 or 4 isn't available to be selected. So the final stats could look like this:

Category 1 - Category 2 - 8 calls
Category 1 - Category 2 - Category 3 - 12 calls.
Category 1 - Category 5 - Category 8 - 15 calls

How do I filter and sort out the top Category 2 numbers (20 calls), but avoid capturing the sums from category Category 5?

I want to avoid AutoFilter because I would like a formula to do all the work for me. Thank you in advance and I apologize if this doesn't make sense. I'll try and clarify if needed. Thanks!

 

[Joe4]

Welcome to the Board!

To Count the number of records with "Category 2" in them, you could use a COUNTIF formula like this:

=COUNTIF(A1:A3,"*Category 2*")

You didn't mention if the number of calls were in the same cell as the Category (is it is all one big long string or covers multiple cells). If you can split the call counts into their own cell, just with the number (let's say into column B), then you could use this formula to count all those Category 2 calls:

=SUMIF(A1:A3,"*Category 2*",B1:B3)

 

[tym623]

Thank you for the welcome! Seems like a great board.

Yes, the number totals are in a separate cell so it appears the SUMIF would work the best.

Is is possible to have "*Category 2*" pre-fill with a random variable? I'm not sure what the title when be in any given row, therefore I would like it to prefill with a given cell. I also don't know how often it will change so I would like the formula to know when the title changes again and what the new title would be. Is this possible? If I could find out when/if it changes, I would like this stored in a random cell. Say F7?

Thanks again for all your help!

 

[Joe4]

Sure!

Following your example, they could enter the value the want to look for in F7 (i.e. Category 2).

The formula would then just need to be altered to look like this:
=SUMIF(A1:A3,"*" & F7 & "*",B1:B3)

 

[tym623]

I'm actually looking for not inputting anything in F7. I'll try and type out what excel would look like. I'll separate cells with parenthesis. <?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Row 1 - (Random Category) (Random Category 2)(empty)(empty)(57) – the last number is the total<o></o>
Row 2 – (Random Category 8)(Random Category 5)(Random Cat. 4)(empty)(13)<o></o>
<o> </o>
I'm thinking the formula would look something like…<o></o>
<o> </o>
=SUMIF(A1:A3,"*" & (Go look for variable somewhere) & "*",B1:B3)<o></o>
<o> </o>
Then let's say it finds 'Random Category 2', then it changes halfway down the sheet to a different variable and would recalculate a then sum based on the changes categories. <o></o>
<o> </o>
Thanks again! <o></o>

 

[Joe4]

I am afraid I don't understand.
What was that mention of F7 in the previous post then?
How does it know what to look for or sum up? What is telling it that?

 

[tym623]

My apologizes. I'm fairly new to excel so i'm sure i'm explaining it very poorly. F7 was mentioned as an output cell.

That's my question, how can I have a formula which can look in column B let's say.....identify the category then sum the last column. Then when the category changes, the formula would know and adjust what's its looking for again. Maybe it's a circular reference?

Sorry for being confusing, thanks again for your help!

 

[Joe4]

Sorry, it still seems a bit unclear.

When you say look at column "B", on which row?
The value for column B could be different depending on which row you are looking at, right?
So how do you determine which one you want to return the sum for?
Do you want to only sum the values for column "B" equals your selected criteria, or where any column in any record matches that value?

Perhaps an example will make more sense. We specifically would need to see two things:

1. A sample data set;

2. What you expected output would be based on that sample data set.

You can post screen images using the tools mentioned here: http://www.mrexcel.com/forum/showthread.php?t=508133

 

[tym623]

Sheet1

<TABLE style="BACKGROUND-COLOR: #ffffff; PADDING-LEFT: 2pt; PADDING-RIGHT: 2pt; FONT-FAMILY: Calibri,Arial; FONT-SIZE: 11pt" border=1 cellSpacing=0 cellPadding=0><COLGROUP><COL style="WIDTH: 30px; FONT-WEIGHT: bold"><COL style="WIDTH: 179px"><COL style="WIDTH: 103px"><COL style="WIDTH: 103px"><COL style="WIDTH: 103px"><COL style="WIDTH: 103px"></COLGROUP><TBODY><TR style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt; FONT-WEIGHT: bold"><TD> </TD><TD>D</TD><TD>E</TD><TD>F</TD><TD>G</TD><TD>H</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">4</TD><TD>Category 1</TD><TD>Category 2</TD><TD>Category 3</TD><TD>Category 4</TD><TD>Totals</TD></TR><TR style="HEIGHT: 19px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">5</TD><TD>Pizza</TD><TD> </TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">15</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">6</TD><TD>Pizza</TD><TD>Apples</TD><TD> </TD><TD> </TD><TD style="TEXT-ALIGN: right">12</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">7</TD><TD>Pizza</TD><TD>Apples</TD><TD>Ice Cream</TD><TD>Chocolate</TD><TD style="TEXT-ALIGN: right">56</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">8</TD><TD>Pizza</TD><TD>Apples</TD><TD>Ice Cream</TD><TD>Vanilla</TD><TD style="TEXT-ALIGN: right">23</TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">9</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">10</TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">11</TD><TD>Pizza Totals</TD><TD style="TEXT-ALIGN: right">106</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">12</TD><TD>Apple Totals</TD><TD style="TEXT-ALIGN: right">91</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">13</TD><TD>Ice Cream Totals</TD><TD style="TEXT-ALIGN: right">79</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">14</TD><TD>Chocolate Totals</TD><TD style="TEXT-ALIGN: right">56</TD><TD> </TD><TD> </TD><TD> </TD></TR><TR style="HEIGHT: 18px"><TD style="TEXT-ALIGN: center; BACKGROUND-COLOR: #cacaca; FONT-SIZE: 8pt">15</TD><TD>Vanilla Totals</TD><TD style="TEXT-ALIGN: right">23</TD><TD> </TD><TD> </TD><TD> </TD></TR></TBODY></TABLE>
<TABLE style="BORDER-BOTTOM-STYLE: groove; BORDER-BOTTOM-COLOR: #00ff00; BORDER-RIGHT-STYLE: groove; BACKGROUND-COLOR: #fffcf9; BORDER-TOP-COLOR: #00ff00; FONT-FAMILY: Arial; BORDER-TOP-STYLE: groove; COLOR: #000000; BORDER-RIGHT-COLOR: #00ff00; FONT-SIZE: 10pt; BORDER-LEFT-STYLE: groove; BORDER-LEFT-COLOR: #00ff00"><TBODY><TR><TD>Spreadsheet Formulas</TD></TR><TR><TD><TABLE style="FONT-FAMILY: Arial; FONT-SIZE: 9pt" border=1 cellSpacing=0 cellPadding=2><TBODY><TR style="BACKGROUND-COLOR: #cacaca; FONT-SIZE: 10pt"><TD>Cell</TD><TD>Formula</TD></TR><TR><TD>E11</TD><TD>=SUMIF(D5:D8,"*" & D5 & "*",$H$5:$H$8)</TD></TR><TR><TD>E12</TD><TD>=SUMIF($E$5:$E$8,"*" & E6 & "*",$H$5:$H$8)</TD></TR><TR><TD>E13</TD><TD>=SUMIF(F5:F8,"*" & F8 & "*",$H$5:$H$8)</TD></TR><TR><TD>E14</TD><TD>=SUMIF(G5:G8,"*" & G7 & "*",$H$5:$H$8)</TD></TR><TR><TD>E15</TD><TD>=SUMIF(G5:G8,"*" & G8 & "*",$H$5:$H$8)</TD></TR></TBODY></TABLE></TD></TR></TBODY></TABLE>

Excel tables to the web >> http://www.excel-jeanie-html.de/index.php?f=1" target="_blank"> Excel Jeanie HTML 4


The formula makes sense when I know what the variables are going to be and when they change. I would like the formula to change in the variable it's looking for whenever the categories change. It's not always going to be 'Pizza' or 'Ice Cream'. The problem is that I don't know what it will be or when it changes in the sheet. The total column will remain constant. Hope that helps.

Thanks again for everything and your patience!

 

[Joe4]

That is a very interesting request. I am not sure how to approach that. In order to make it "dynamic" like you want, I think that may require some VBA. But it could get quite complex, depending on all the details of your data and possible scenarios.

I would say that you might be able to try to use Subtotals or Pivot Tables, but with the interesting layout of your data, I am not sure how it would work with those options.

Maybe I'll see if anyone else has some suggestions...