Summaries based on criteria

A

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

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
 
C

Claus Busch

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

(Cell A2) No of People where status = A
(Cell A3) Total Price =
Click to expand...

your data sheet is Sheet1, then for people with status A:
=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
 
A

Andy Roberts

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
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

(Cell A2) No of People where status = A
(Cell A3) Total Price =
Click to expand...

your data sheet is Sheet1, then for people with status A:
=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
 
A

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

=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
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

(Cell A2) No of People where status = A
(Cell A3) Total Price =
Click to expand...

your data sheet is Sheet1, then for people with status A:
=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
 
C

Claus Busch

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
Click to expand...

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.


Regards
Claus Busch
 
C

Claus Busch

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
Click to expand...

I think there is a typo anywhere.
Please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Andy" or rightclick and download it.


Regards
Claus Busch
 
A

Andy Roberts

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
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
Click to expand...

I think there is a typo anywhere.
Please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Andy" or rightclick and download it.


Regards
Claus Busch
 
A

Andy Roberts

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
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
Click to expand...

I think there is a typo anywhere.
Please look here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
for the workbook "Andy" or rightclick and download it.


Regards
Claus Busch
 
C

Claus Busch

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?
Click to expand...

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.


Regards
Claus Busch
 
A

Andy Roberts

Perfect, many thanks again

Regards

Andy

Andy Roberts
Win 7 Pro
Office 2010
"Claus Busch" wrote in message
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?
Click to expand...

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.


Regards
Claus Busch
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Working Days in a Named Month 1
Data Validation List controlled by Cell Value 6
Formula to fill cells based on Date of Arrival and Date of Departure 1
Searching for last cell address in a row 1
Add Hours by Looping through Data Q 27
Counting & Matching Text 1
Linking Cell based on Date 8
Excel date intervals look up 1

Top