Hi, i have 2 columns Imp and Stat in Out Worksheet. I use SUBTOTAL to count the number of columns for Stat. Now I want to know how am i able to store the total subtotal to a unique Imp which I want to output later to a graph. The graph will show how many stats handled by each imp. Any help is welcomed. Thanks
post a example file.
This formula counts unique entries in a1:A20Originally Posted by DarReNz
=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
i am trying to think of an idea how to do it. i want to have a graph that show the whole picture meaning Carol has 1 Pend and Jack has 2 Wait. Besides that, I also want to have individual graphs on Carol and Jack too. The thought i can think of was from the above using SUBTOTAL to count entries when the worksheet is filtered. Now i need to tie those entries to Carol, Jack from Imp column. Any ideas ?
eg.
Imp Stat
Carol Pend
Jack Wait
Jack Pend
=SUMPRODUCT(--(A1:A20="Carol"),--(A1:A20="Pend"))
just extend for all possibilities, or pivot table it
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
xld, is it possible to collect the entries from another worksheet ?
Yes.
=SUMPRODUCT(--(Sheet2!A1:A20="Carol"),--(Sheet2!A1:A20="Pend"))
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
xld, I used SUBTOTAL for the number of entries in the column(Bau worksheet) is correct, however when i used SUMPRODUCT it gave me an incorrect count. The formula for SUBTOTAL that I used is =SUBTOTAL(2, A3:A65536). I used A column here because the column is Number and I auto numbered it accordingly, therefore able to count how many entries. I was planning to use column F however the entries are all in words(Pend, Wait, etc).
But i figure i can't use SUBTOTAL as everytime the worksheet is filtered the count gets changed therefore i can't keep track of how many entries by each Imp. Therefore do i still use SUMPRODUCT to count for this ? I am still not sure how you count using it. Thanks
Eg. Param Worksheet
Imp Cases(Number of Entries)
Jack 6
Carol 4
Sam 3
Is it possible to post a sample file ?
What SUMPRODUCT formula did you use and what result did you get?
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I used this formula
=SUMPRODUCT((A1:A20<>"")/COUNTIF(A1:A20,A1:A20&""))
Here is the workbook. As you can see I used the formula in L1 and J1 respectively in Bau worksheet. When you "Search By Imp", the worksheet is filtered and the values in L1 and J1(Total Entries) will change accordingly. I actually want to use this formulas under Cases column in Param Worksheet. Therefore, it shows Jack having 3 entries, and the rest having only 1. Any help ? Thanks
I don't see what is wrong with the SUBTOTAL formula you are using, it seems to provide the correct result to me.
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
hi i use this formula =SUMPRODUCT(--(Bau!A2:A20<>"")/COUNTIF(A2:A20,A2:A20&"")) on Param worksheet but it doesn't seems to count on Bau worksheet and instead it still counts on Param worksheet. How do i correct this ?
Qualify all ranges
=SUMPRODUCT(--(Bau!A2:A20<>"")/COUNTIF(Bau!A2:A20,Bau!A2:A20&""))
____________________________________________
Nihil simul inventum est et perfectum
Abusus non tollit usum
Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber
I think I know what are you trying to do
For the Input worksheet in cell B13 try using this formula:
=SUMPRODUCT(--(Bau!G2:G7=B11)*(Bau!F2:F7=Input!E5),Bau!H2:H7)
and in Param worksheet use this formula in cells C2:C7:
=COUNTIF(Bau!F$2:F$7,B2)
Also I notice that when I tried to use the formulas it was not calculationg properly so I use this code to clean up the data that you have in the worksheet. When that happens when you import data from somewhere sometimes it will bring unseen junk data on to your worksheet. Also I advise for you to use Data Validation Drop-down list the formulas responds better.
[VBA]Sub Clean_Trim() Dim CleanTrimRg As Range
Dim oCell As Range
Dim Func As WorksheetFunction
Set Func = Application.WorksheetFunction
On Error Resume Next
Set CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)
If Err Then MsgBox "No data to clean and Trim!": Exit Sub
For Each oCell In CleanTrimRg
oCell = Func.Clean(Func.Trim(oCell))
Next
End Sub
[/VBA]
Hi Shazam i couldn't run the Clean_Trim(), it kept giving me a syntax error for this line Sub Clean_Trim() Dim CleanTrimRg As Range.
Just wondering can i collect entries from another worksheet using SUBTOTAL like this
=SUBTOTAL(--Bau!(2, A3:A65536)) ? It doesn't seem to work.
And the SUMPRODUCT still doesn't give me the value I want. I just want an equilavent of =SUBTOTAL(2, A3:A65536) for SUMPRODUCT.
Just wanted to make sure does SUMPRODUCT only counts unique entries based on words ? This is because there are columns i need to count consists of words and they may be similar, therefore I don't want to count unique for them.
Sorry for the code I pasted it worng.
Ok the SUBTOTAL Formula works only if you have use the Subtotal function on your worksheet in the tool bar and go to the Data menue. The Subtotal Formula only reads other Subtotal Formulas.
There is one thing I could think that it might give you the flexibilty you want. Look at the attachment below. But its only availible in Excel 2003. If you go to Data menue and choose create List it will apply Filters for each columns and drop down list of Subtotals formulas.
This formula will count how many jacks in a column.
=COUNTIF(Bau!F$2:F$7,"jack")
Look at the Attachment below and see if this works for you.
[VBA]Sub Clean_Trim()
Dim CleanTrimRg As Range
Dim oCell As RangeDim Func As WorksheetFunction
Set Func = Application.WorksheetFunction
On Error Resume NextSet CleanTrimRg = Selection.SpecialCells(xlCellTypeConstants, 2)If Err Then MsgBox "No data to clean and Trim!": Exit Sub
For Each oCell In CleanTrimRgoCell = Func.Clean(Func.Trim(oCell))Next
End Sub[/VBA]
hi Shazam
I will try to give u a clear picture of what i want. As you can see in the Param worksheet under Cases column, I have these
Implementer Cases
Jack 3
Eric 1
Sam 1
Cool 1
King 0
How do i get these values from Cases ? Firstly, I select any value from the drop down list(Search By Imp) at Input worksheet. Then at Bau worksheet you can see how many entries are there. If I select Jack, it will show you 3 entries. I want a formula or code that can store or keep track of these entries under Cases. I don't think SUBTOTAL and COUNTIF will work because I tried them. As for SUMPRODUCT, I am not sure how do i achieve those Cases using this formula. I hope you understand what I am trying to do. Need help .....
Posting Permissions
Reply With Quote
