Search and sum

[JackM95]

Hi all,

I am struggling to try and find a formula that will help me with what I am trying to achieve.

Basically what I would like to have is a summary on a separate sheet that shows totals of specific headings, extracted from a report on another sheet.

The thing that is troubling me is that the columns change when the report is run so for example, the heading 'Expenses' that might be in column D one month, is now in column G in the next month. Additionally, the number of rows also change month to month depending on how many people there are. The only constant in this is that row 7 is ALWAYS the row that has the headings. Please see below an example:

Sheet 1

Sheet 2

Any help would be greatly appreciated.

Thanks

 

[etaf]

you can use
=SUMPRODUCT(($C$7:$F$7=H7)*($C$8:$F$40))
BUT add the sheet names in

Book2
	A	B	C	D	E	F	G	H	I
1
2
3
4
5
6
7			food	rent	expenses	travel		food	4
8		Person1						rent	12
9		Person2	1		3	4		expenses	15
10		Person3	1	2	3	4		travel	20
11		Person4	1	2	3	4
12		Person5	1	2	3	4
13		Person6		2	3	4
14		Person7		2
15		Person8		2
16		Person9
17		Person10
18		Person11
19		Person12
20		Person13
21		Person14
22		Person15
23		Person16
24		Person17
25		Person18
26		Person19
27		Person20
28		Person21
29		Person22
30
Sheet1

Cell Formulas
Range	Formula
H7:H10	H7	=TOCOL(C7:F7)
I7:I10	I7	=SUMPRODUCT(($C$7:$F$7=H7)*($C$8:$F$40))
Dynamic array formulas.

 

[Joe4]

If you truly have a totals row at the bottom of each column, you may need to divide etaf's SUMPRODUCT total formula by 2, i.e.

=SUMPRODUCT(($C$7:$F$7=H7)*($C$8:$F$40))/2

 

[etaf]

thanks Joe4 - missed that, possibility

 

[FormR]

Hi, here's another way you could try where you can use full column references without a big hit to performance.

Book1
	A	B	C	D	E	F	G	H	I
1
2
3
4
5
6
7			food	rent	expenses	travel		food	4
8		Person1						rent	12
9		Person2	1		3	4		expenses	15
10		Person3	1	2	3	4		travel	20
11		Person4	1	2	3	4
12		Person5	1	2	3	4
13		Person6		2	3	4
14		Person7		2
15		Person8		2
16		Person9
17		Person10
18		Person11
19		Person12
20		Person13
21		Person14
22		Person15
23		Person16
24		Person17
25		Person18
26		Person19
27		Person20
28		Person21
29		Person22
30			4	12	15	20
Sheet1

Cell Formulas
Range	Formula
H7:H10	H7	=TOCOL(C7:F7)
I7:I10	I7	=SUM(XLOOKUP(H7,$C$7:$F$7,C:F))/2
Dynamic array formulas.

 

[JackM95]

Thanks for the help both.

It works on the example workbook I created however when I try and do the same thing in the workbook I need it in, the value returns with #N/A. Looking at the formula evaluation tool, it picks up the heading correctly in the first half of the formula but it doesn't seem to like the second part of the formula. It only seems to work when I do the range on that specific column (below - column P) but if I try to add in any extra columns, I get the #N/A. Below I have done the full table but even if I in the second part of the formula I do (PS!$O$8:$P$40), it doesn't like it.

Would you know why this might be? Hopefully I explained this well enough for you to understand!

Thanks again for the help.

 

[JackM95]

Hi, here's another way you could try where you can use full column references without a big hit to performance.

Book1
	A	B	C	D	E	F	G	H	I
1
2
3
4
5
6
7			food	rent	expenses	travel		food	4
8		Person1						rent	12
9		Person2	1		3	4		expenses	15
10		Person3	1	2	3	4		travel	20
11		Person4	1	2	3	4
12		Person5	1	2	3	4
13		Person6		2	3	4
14		Person7		2
15		Person8		2
16		Person9
17		Person10
18		Person11
19		Person12
20		Person13
21		Person14
22		Person15
23		Person16
24		Person17
25		Person18
26		Person19
27		Person20
28		Person21
29		Person22
30			4	12	15	20
Sheet1

Cell Formulas
Range	Formula
H7:H10	H7	=TOCOL(C7:F7)
I7:I10	I7	=SUM(XLOOKUP(H7,$C$7:$F$7,C:F))/2
Dynamic array formulas.

Actually I think this works. Seems to work on my spreadsheet but will try on another and see if I get the same result.

Thank you

 

[etaf]

you are looking for TOTAL
does that exist in A7:AO7
does it have any spaces after total ?
often extracted info can have spaces and so you get the error

the range is P to P
NOT the same as the headers
A8:AO40
to match the headers

difficult with images which do not match the test
(PS!$O$8:$P$40),
how does that match with the headers

 

[JackM95]

Hi all

Thanks for all your help, the formula given seems to work.

Really appreciate you taking the time to help me.

Jack

 

[etaf]

you are welcome