Google Groups no longer supports new Usenet posts or subscriptions. Historical content remains viewable.
Dismiss
Summaries based on criteria
3 views
Skip to first unread message
Andy Roberts
Jun 18, 2013, 11:10:49 AM6/18/13
to
I has 2 sheets, one with all my data and a second which summaries it.
In my data sheet I have the following columns
Date Name Status Price
01/01/11 Mr Smith A 10.00
01/02/12 Mr Jones A 15.00
01/03/12 Mr Tims B 12.00
01/04/13 Mr Roberts C 14.00
01/05/13 Mr Davis C 10.00
What I want to be able to do on my summary sheet is have a dropdown with a
year in (cell A1) which I select and the data summaries for that year as
follows:-
(Cell A2) No of People where status = A
(Cell A3) Total Price =
Hopefully my explanation makes sense
Regards
Andy
Andy Roberts
Win 7 Pro
Office 2010
In my data sheet I have the following columns
Date Name Status Price
01/01/11 Mr Smith A 10.00
01/02/12 Mr Jones A 15.00
01/03/12 Mr Tims B 12.00
01/04/13 Mr Roberts C 14.00
01/05/13 Mr Davis C 10.00
What I want to be able to do on my summary sheet is have a dropdown with a
year in (cell A1) which I select and the data summaries for that year as
follows:-
(Cell A2) No of People where status = A
(Cell A3) Total Price =
Hopefully my explanation makes sense
Regards
Andy
Andy Roberts
Win 7 Pro
Office 2010
Claus Busch
Jun 18, 2013, 11:20:06 AM6/18/13
to
Hi Andy,
Am Tue, 18 Jun 2013 16:10:49 +0100 schrieb Andy Roberts:
> I has 2 sheets, one with all my data and a second which summaries it.
>
> In my data sheet I have the following columns
>
> Date Name Status Price
> 01/01/11 Mr Smith A 10.00
> 01/02/12 Mr Jones A 15.00
> 01/03/12 Mr Tims B 12.00
> 01/04/13 Mr Roberts C 14.00
> 01/05/13 Mr Davis C 10.00
>
=SUMPRODUCT(--(YEAR(Sheet1!A2:A100)=A1),--(Sheet1!C2:C100="A"))
and for total price of the year:
=SUMPRODUCT(--(YEAR(Sheet1!A2:A100)=A1),Sheet1!D2:D100)
Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Am Tue, 18 Jun 2013 16:10:49 +0100 schrieb Andy Roberts:
> I has 2 sheets, one with all my data and a second which summaries it.
>
> In my data sheet I have the following columns
>
> Date Name Status Price
> 01/01/11 Mr Smith A 10.00
> 01/02/12 Mr Jones A 15.00
> 01/03/12 Mr Tims B 12.00
> 01/04/13 Mr Roberts C 14.00
> 01/05/13 Mr Davis C 10.00
>
> (Cell A2) No of People where status = A
> (Cell A3) Total Price =
your data sheet is Sheet1, then for people with status A:
> (Cell A3) Total Price =
=SUMPRODUCT(--(YEAR(Sheet1!A2:A100)=A1),--(Sheet1!C2:C100="A"))
and for total price of the year:
=SUMPRODUCT(--(YEAR(Sheet1!A2:A100)=A1),Sheet1!D2:D100)
Regards
Claus Busch
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2
Andy Roberts
Jun 18, 2013, 12:24:49 PM6/18/13
to
Thanks Claus that's spot on.
For my year dropdown list I would like to it to be populated with a list of
years from all the dates in the data sheet...
01/01/13
01/01/14
02/02/14
03/03/15
03/03/15
... would give me a list (hopefully sorted descending) of 2015,2014,2013
Regards
Andy
Andy Roberts
Win 7 Pro
Office 2010
"Claus Busch" wrote in message news:kpptra$tko$1...@news.albasani.net...
For my year dropdown list I would like to it to be populated with a list of
years from all the dates in the data sheet...
01/01/13
01/01/14
02/02/14
03/03/15
03/03/15
... would give me a list (hopefully sorted descending) of 2015,2014,2013
Regards
Andy
Andy Roberts
Win 7 Pro
Office 2010
Andy Roberts
Jun 18, 2013, 12:33:01 PM6/18/13
to
Actually Claus I get an #VALUE error.
Here is my exact code. I understand how it is structured and it seems ok.
I've got my data set up as a table and my cell with the year in is F1
=SUMPRODUCT(--(YEAR(JobTable[Date Confirmed])=$F$1),--(JobTable[Current Job
Status])="Job Raised")
=SUMPRODUCT(--(YEAR(JobTable[Date Confirmed])=$F$1),JobTable[Total Fee])
Regards
Andy
Andy Roberts
Win 7 Pro
Office 2010
Here is my exact code. I understand how it is structured and it seems ok.
I've got my data set up as a table and my cell with the year in is F1
=SUMPRODUCT(--(YEAR(JobTable[Date Confirmed])=$F$1),--(JobTable[Current Job
Status])="Job Raised")
=SUMPRODUCT(--(YEAR(JobTable[Date Confirmed])=$F$1),JobTable[Total Fee])
Regards
Andy
Andy Roberts
Win 7 Pro
Office 2010
"Claus Busch" wrote in message news:kpptra$tko$1...@news.albasani.net...
Claus Busch
Jun 18, 2013, 12:33:41 PM6/18/13
to
Hi Andy,
Am Tue, 18 Jun 2013 17:24:49 +0100 schrieb Andy Roberts:
> For my year dropdown list I would like to it to be populated with a list of
> years from all the dates in the data sheet...
>
> 01/01/13
> 01/01/14
> 02/02/14
> 03/03/15
> 03/03/15
write in a helper column =YEAR(A2) and copy down. Then filter with
advanced filter without duplicates to another column, sort descending.
Then select A1 in the summary sheet => Data Validation => List and set
the range with the years as source.
Am Tue, 18 Jun 2013 17:24:49 +0100 schrieb Andy Roberts:
> For my year dropdown list I would like to it to be populated with a list of
> years from all the dates in the data sheet...
>
> 01/01/13
> 01/01/14
> 02/02/14
> 03/03/15
> 03/03/15
advanced filter without duplicates to another column, sort descending.
Then select A1 in the summary sheet => Data Validation => List and set
the range with the years as source.
Claus Busch
Jun 18, 2013, 12:48:22 PM6/18/13
to
Hi Andy,
Am Tue, 18 Jun 2013 17:33:01 +0100 schrieb Andy Roberts:
> Actually Claus I get an #VALUE error.
>
> Here is my exact code. I understand how it is structured and it seems ok.
> I've got my data set up as a table and my cell with the year in is F1
I think there is a typo anywhere.
Please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3%21326
for the workbook "Andy" or rightclick and download it.
Am Tue, 18 Jun 2013 17:33:01 +0100 schrieb Andy Roberts:
> Actually Claus I get an #VALUE error.
>
> Here is my exact code. I understand how it is structured and it seems ok.
> I've got my data set up as a table and my cell with the year in is F1
Please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3%21326
for the workbook "Andy" or rightclick and download it.
Andy Roberts
Jun 18, 2013, 1:03:46 PM6/18/13
to
Claus
Many thanks - it was a pesky ')' in the wrong place
Regards
Andy
Andy Roberts
Win 7 Pro
Office 2010
"Claus Busch" wrote in message news:kpq30r$at6$1...@news.albasani.net...
Many thanks - it was a pesky ')' in the wrong place
Regards
Andy
Andy Roberts
Win 7 Pro
Office 2010
Andy Roberts
Jun 18, 2013, 1:05:47 PM6/18/13
to
Thanks for all your help on this..
To finalise what I need, how do you add to the criteria array. Say for
example I wanted both "A" or "B" as the criteria for filtering?
Regards
Andy
Andy Roberts
Win 7 Pro
Office 2010
"Claus Busch" wrote in message news:kpq30r$at6$1...@news.albasani.net...
To finalise what I need, how do you add to the criteria array. Say for
example I wanted both "A" or "B" as the criteria for filtering?
Regards
Andy
Andy Roberts
Win 7 Pro
Office 2010
Claus Busch
Jun 18, 2013, 1:13:08 PM6/18/13
to
Hi Andy,
Am Tue, 18 Jun 2013 18:05:47 +0100 schrieb Andy Roberts:
> To finalise what I need, how do you add to the criteria array. Say for
> example I wanted both "A" or "B" as the criteria for filtering?
you need A or B for calculating in "Summary"? Then create another Data
Validation e.g. in G1 with List of A and B. In the formula you can
change A to the cell address. Or look again into the workbook, I changed
it there.
Am Tue, 18 Jun 2013 18:05:47 +0100 schrieb Andy Roberts:
> To finalise what I need, how do you add to the criteria array. Say for
> example I wanted both "A" or "B" as the criteria for filtering?
Validation e.g. in G1 with List of A and B. In the formula you can
change A to the cell address. Or look again into the workbook, I changed
it there.
Andy Roberts
Jun 18, 2013, 1:15:33 PM6/18/13
to
Perfect, many thanks again
Regards
Andy
Andy Roberts
Win 7 Pro
Office 2010
"Claus Busch" wrote in message news:kpq4f9$cj1$1...@news.albasani.net...
Regards
Andy
Andy Roberts
Win 7 Pro
Office 2010
0 new messages