I need help generating a summary page.

[a15457]

Hi All,

I have a work book that I'm building that will summarise important data from other sheets within the same workbook. What I need help with is building the count / If / Sum formulas to grab the info that I want.

The Summary sheet looks like this:

Book1
	A	B	C
1	Summary
2		OverDue	Due By date
3	Data1
4	Data2
5	Data3
Summary

The "Data1" Sheet looks like this:

Book1
	A	B	C	D
1	ID	Amount	Date	Paid
2	1	10	20/02/2015	Yes
3	2	20	20/03/2015	Yes
4	3	30	20/04/2015	Yes
5	4	40	20/05/2015	Yes
6	5	50	20/06/2015	No
7	6	60	20/07/2015	Yes
8	7	70	20/08/2015	Yes
9	8	80	20/09/2015	Yes
10	9	90	20/10/2015	Yes
11	10	100	20/11/2015	Yes
12	11	110	20/12/2015	No
13	12	120	20/01/2016	No
14	13	130	20/02/2016	Yes
15	14	140	20/03/2016	Yes
16	15	150	20/04/2016	Yes
Data1

The Data2 Sheet looks like this:

Book1
	A	B	C	D
1	ID	Amount	Date	Paid
2	1	10	20/02/2015	Yes
3	2	20	20/03/2015	Yes
4	3	30	20/04/2015	Yes
5	4	40	20/05/2015	Yes
6	5	50	20/06/2015	Yes
7	6	60	20/07/2015	Yes
8	7	70	20/08/2015	Yes
9	8	80	20/09/2015	Yes
10	9	90	20/10/2015	Yes
11	10	100	20/11/2015	Yes
12	11	110	20/12/2015	Yes
13	12	120	20/01/2016	Yes
14	13	130	20/02/2016	Yes
15	14	140	20/03/2016	No
16	15	150	20/04/2016	Yes
Data2

What I want to do is add up the values from the datasheets column B (Amount) IF the value in column D (Paid) is "No".

So for "Data1" I want it to add up B6, B12, and B13 and this total will go into B3 on the Summary Sheet.

For "Data2" I want it to add up B15 and this total will go into B4 on the Summary Sheet.

After that I need to grab the oldest date in a Data sheet where (Paid) is "No" and put that into C3 on the summary sheet, "20/06/2015" for "Data1", and "20/03/2016" for "Data2". It should also be noted that the dates may not necessarily be in order in the data sheets.

I'm using Excel 2013 on windows 7 x64.

Hopefully I've been clear on what I want and that it's not too hard to work out. I just don't know where to start. It's been a while since I've played in Excel land.

Cheers,

Dave

 

[humdingaling]

depends how fancy you want to get
the overdue can be handled by a sumif formula
=SUMIF(Data1!D:D,"No",Data1!B:B)

however you need to manually update the sheet reference
you can get fancy and use an indirect to point to the sheet if you want
=SUMIF(INDIRECT(A3&"!D:D"),"No",INDIRECT(A3&"!B:B"))
where a3 = sheet name

For due date
you can do it with a CSE formula...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer. Press F2 on that cell and try again.

=INDEX(Data1!C2:C16,SMALL(IF(Data1!D2:D16="No",ROW(Data1!D2:D16)-1),1))

you can apply the same principle of indirect but it gets a bit fiddly

=INDEX(INDIRECT(A3&"!C2:C16"),SMALL(IF(INDIRECT(A3&"!D2:D16")="No",ROW(D2:D16)-1),1))

 

[humdingaling]

i duplicated your scenario and entered the formulas
you can download the file here

https://drive.google.com/file/d/0B5iSSIFE0WGKWGgtU2d4ZzBTalE/view?usp=sharing

 

[a15457]

Thank you very much Humdingaling, that's almost perfect.

From the sample spreadsheet that you shared, if you change the date of row 13 (Cell C13) to 20/01/2013 and "No" was in D13 I would expect that date to show up on the summary page but it doesn't instead I get the date "20/03/2015" from Cell C6.

The other oddness that I've noticed and something that I should have thought of initially if there are no items unpaid so all of column D is Yes then I need it to say something like "No Outstanding" in the OverDue and due By Date columns on the summary page. What I noticed when I filled Yes through all of column D it would just show 0 in overdue, which is kind of OK and I can work with that, but the Due by date seems to select the date from the second row down of data.

Cheers,

Dave

 

[humdingaling]

sorry i did the formula back to front

=IFERROR(SMALL(INDEX(INDIRECT(A3&"!C2:C16"),(IF(INDIRECT(A3&"!D2:D16")="No",ROW(D2:D16)-1))),1),"Missing")

the INDEX needs to be first not the SMALL

now of the other condition
add another if infront to check if B is zero

=IF(B3=0,"No outstanding",IFERROR(SMALL(INDEX(INDIRECT(A3&"!C2:C16"),(IF(INDIRECT(A3&"!D2:D16")="No",ROW(D2:D16)-1))),1),"Missing"))

file updated
https://drive.google.com/file/d/0B5iSSIFE0WGKdzZWVjY2TzVFejA/view?usp=sharing

 

[a15457]

Brilliant!

Thanks Humdingaling!

There's no way I would have worked that out myself!

:D

 

[humdingaling]

not a problem
glad to have helped

 

[humdingaling]

Hi
sorry something wrong with the logic previously
check out the latest file with the amended formula

https://drive.google.com/file/d/0B5iSSIFE0WGKT013TUl4Tkhob00/view?usp=sharing

 

[a15457]

Thanks so much!!!!