Lookup,count and pie chart Excel2007 [Solved/Closed]

Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
- - Latest reply:  Gouws - Mar 1, 2010 at 07:33 AM
Hello,
Can anyone give advice?
I got a form on sheet1 thats got a savebutton to save the input data of the form to the database on sheet2.On sheet2 row1 i got headings in Columns A1 to J1. A1=StpDate&Time(dd,mm,yyyy hh:mm) ,B1=event,C1=Stage,D1=No.,E1=Descrip,F1=Types,G1=Stype,H1=Comm,I1=StrDate&Time(dd,mm,yyyy hh:mm), J1=StpDate&Time minus StrDate&Time( [h]:mm:ss )

I want to lookup a specific month(Jan-Dec)in column A , count the amount of different Types in column G (Types=Inc,Circ,Sched,Oth1) and also add the time for the differnt types from column J where i subtract I2 from A2,I3-A3......etc
I need to get this to sheet 3 to make a pie chart

I've put filters in on the columns in Sheet 2 and can get the specific month.Is there a way that i can use the filtered data and then count and add them and use them in a pie chart.
Tx G
See more 

1 reply

Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
753
0
Thank you
Could you provide a sample data? may be upload a sample file at some share site like http://www.speedyshare.com/
Gouws
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
-
rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
753 > Gouws
Posts
45
Registration date
Sunday February 7, 2010
Status
Member
Last seen
April 15, 2012
-
If you want to get a total of visible range then you have to use subtotal. Your case do have a little more turn and twist as your want to total but grouped and conditioned based

This formula would allow you do get count and sum under such conditions would be

For Count:
=SUMPRODUCT(($E$2:$E$15="Incident")*SUBTOTAL(3,OFFSET($B$2,ROW($B$2:$B$15)-ROW($B$2),)))

Basically saying that only interested in rows that have word "Incident" in range e2:15 and are visible and I am only interested in getting a count of cells in range b2:b15


For Sum:
=SUMPRODUCT(($E$2:$E$15="Incident")*SUBTOTAL(9,OFFSET($J$2,ROW($J$2:$J$15)-ROW($J$2),)))

Basically saying that only interested in rows that have word "Incident" in range e2:15 and are visible and I need sum of range j2:j15


Since your graph is based on these totals as you filter your data, the values would change and such changes would be reflected in your graph

I have used your file to add those formulas to "explanation" tab.

http://www.speedyshare.com/files/21179787/GraphFormula.xlsm
> rizvisa1
Posts
4475
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
January 6, 2016
-
TX rivisa1
Did not take the sheet off yet, used only the formulas so far and it works GREAT,TX,G