Previous Topic Next Topic

Lookup,count and pie chart Excel2007

Solved/Closed
Gouws Posts 45 Registration date Sunday February 7, 2010 Status Member Last seen April 15, 2012 - Feb 25, 2010 at 01:35 AM
 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
Related:

1 response

Answer 1 / 1
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Feb 25, 2010 at 04:56 AM
Could you provide a sample data? may be upload a sample file at some share site like https://authentification.site
0
Gouws Posts 45 Registration date Sunday February 7, 2010 Status Member Last seen April 15, 2012
Feb 26, 2010 at 01:19 AM
To riviza1
https://authentification.site/files/21143785/WO_sample.xlsm

TX
G

nevujufecife
0
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766 > Gouws Posts 45 Registration date Sunday February 7, 2010 Status Member Last seen April 15, 2012
Feb 28, 2010 at 05:42 AM
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
0
Gouws > rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022
Mar 1, 2010 at 07:33 AM
TX rivisa1
Did not take the sheet off yet, used only the formulas so far and it works GREAT,TX,G
0

Similar discussions

help me to add particular cells
mathewmunna -
mathew munna -

2 responses
Counting cells containing only numbers
jutulu -
jutulu -

4 responses