Sum across worksheets multiple criterias

[Fin Fang Foom]

Hi everyone I really need some help.

I'm trying to get this formula below to work across multiple worksheets.

=SUM(IF(RIGHT(C3:C15,5)<>"Total",IF(D1:G1="Bonus",IF(ISNUMBER(D3:G15),D3:G15))))


So what I did I tried to modified into this?

=SUM(IF(RIGHT(OFFSET(INDIRECT("'"&C2:D2&"'!C3:C15")
,ROW(INDIRECT("3:15"))-3,0,1)),5)<>"Total",
IF(N(OFFSET(INDIRECT("'"&C2:D2&"'!D1:G1"),
ROW(INDIRECT("3:15"))-3,0,1))="Bonus",
ISNUMBER(N(OFFSET(INDIRECT("'"&C2:D2&"'!D3:G15"),
ROW(INDIRECT("3:15"))-3,0,1))),
N(OFFSET(INDIRECT("'"&C2:D2&"'!D3:G15"),ROW(INDIRECT("3:15"))-3,0,1))))

But it gives me a value error.

I can't use any Add-ins or vba for this. Hopefully we could get this formula to work above.


Here is an small example I Have, If you have notice the word bonus appears in 2 different columns.

Sum Across Worksheets.xls
	A	B	C	D	E	F	G
1				Hours	Down	Bonus
2	Date	SH	Clock	Worked	Time	Hours
3	1050622	2	43	3	0	-1.20
4	1050622	2	43	5.25	0.48	-0.18
5	1050622	2	43	8.25	1	-0.17
6	1050622	2	43	1.2	0.2	0.29
7			43 Total	3.5		-1.26
8
9	1050622	2	44	3.18	0.83	-2.33
10			44 Total	8.51		-2.33
11
12	1050622	2	45	0.37	1	0.23
13	1050622	2	45	2.62	0	1.69
14	1050622	2	45	1.55	0.48	4.01
15			45 Total	2.87		5.94
16
Sheet2

Sum Across Worksheets.xls
	A	B	C	D	E	F
1				Down	Bonus
2	Date	SH	Clock	Time	Hours
3	1050622	1	21	1.43	0.43
4	1050622	1	21	1.10	0.45
5	1050622	1	21	1.49	-0.11
6			21 Total		0.77
7
8	1050622	1	27	6.30	2.60
9	1050622	1	27	0.67	0.19
10	1050622	1	27	1.94	0.42
11	1050622	1	27	2.75	0.75
12			27 Total		3.97
13
Sheet3

 

[Fin Fang Foom]

bump!

 

[ravishankar]

Hi
this formula gives sum of 3 sheets range A2 to B12. you can probably modify it to suit your data range.

=SUM(Sheet1:Sheet3!A2:B12)
Ravi

 

[Fin Fang Foom]

Hi
this formula gives sum of 3 sheets range A2 to B12. you can probably modify it to suit your data range.

=SUM(Sheet1:Sheet3!A2:B12)
Ravi

Thanks ravishankar But the formula I'm trying to devise is a bit more complicated.

 

[Fin Fang Foom]

I dont know what I'm missing I adjust the OFFSET function Width it should work but it does not.

Any Ideas?

=SUM(IF(RIGHT(T(OFFSET(INDIRECT("'"&$C$2:$C$3&"'!C3"),
COLUMN(INDIRECT("3:15"))-3,0,1)),5)<>"Total",
IF(T(OFFSET(INDIRECT("'"&$C$2:$C$3&"'!D1"),
COLUMN(INDIRECT("1:1"))-1,0,1,5))="Bonus",
ISNUMBER(N(OFFSET(INDIRECT("'"&$C$2:$C$3&"'!D3"),
COLUMN(INDIRECT("3:15"))-3,0,15,20))),
N(OFFSET(INDIRECT("'"&$C$2:$C$3&"'!D3"),
COLUMN(INDIRECT("3:15"))-3,0,15,20)))))

 

[Domenic]

Defined Names

ArrayA:

=COUNTIF(OFFSET(INDIRECT("'"&Sheet1!$C$2:$D$2&"'!C3:C15"),ROW(INDIRECT("3:15"))-3,,1),"<>*")

ArrayB:

=N(OFFSET(INDIRECT("'"&Sheet1!$C$2:$D$2&"'!"&Col),ROW(INDIRECT("3:15"))-3,,1))

Col:

=CHOOSE(Pos,"D3:D15","E3:E15","F3:F15","G3:G15")

Pos:

=TRANSPOSE(MMULT((T(OFFSET(INDIRECT("'"&TRANSPOSE(Sheet1!$C$2:$D$2)&"'!D1:G1"),,COLUMN(INDIRECT("D:G"))-4,,1))="Bonus")*(COLUMN(INDIRECT("D:G"))-4+1),TRANSPOSE(COLUMN(INDIRECT("D:G"))^0)))

Formula

=SUM(IF(ArrayA,ArrayB))

...confirmed with CONTROL+SHIFT+ENTER.

Hope this helps!

 

[Fin Fang Foom]

WOW ray: Domenic!!


How long it took you to come up with that? Its perfect!! Thank You!

One more thing when I tried to expand ranges to Column D through Column X I get a #REF! error. Here are the formulas I tried to modified.

ArrayA =COUNTIF(OFFSET(INDIRECT("'"&Sheet1!$C$2:$E$2&"'!C3:C200"),ROW(INDIRECT("3:200"))-3,,1),"<>*")
ArrayB =N(OFFSET(INDIRECT("'"&Sheet1!$C$2:$E$2&"'!"&Col),ROW(INDIRECT("3:200"))-3,,1))
Col =CHOOSE(Pos,"D3:D200","E3:E200","F3:F200","G3:G200","H3:H200","I3:I200","J3:J2005","K3:K200","L3:L200","M3:M200","N3:N200","O3:O200","Q3:Q200","R3:R200","S3:S200","T3:T200","U3:U200","V3:V200","W3:W200","x3:X200")
Pos =TRANSPOSE(MMULT((T(OFFSET(INDIRECT("'"&TRANSPOSE(Sheet1!$C$2:$E$2)&"'!D1:X1"),,COLUMN(INDIRECT("D:X"))-15,,1))="Bonus")*(COLUMN(INDIRECT("D:X"))-15+1),TRANSPOSE(COLUMN(INDIRECT("D:X"))^0)))


Am I missing something?

 

[Domenic]

...Thank You!...

You're very welcome!

Pos =TRANSPOSE(MMULT((T(OFFSET(INDIRECT("'"&TRANSPOSE(Sheet1!$C$2:$E$2)&"'!D1:X1"),,COLUMN(INDIRECT("D:X"))-15,,1))="Bonus")*(COLUMN(INDIRECT("D:X"))-15+1),TRANSPOSE(COLUMN(INDIRECT("D:X"))^0)))

The number 15 should remain 4. Instead of 4 (now 15), I probably should have used the following...

COLUMN(INDIRECT("D:D"))

It would have made it easier to adapt it to your spreadsheet.

 

[Fin Fang Foom]

Hi Domenic,


I changed the column reference but still getting the #REF! error.


Pos =TRANSPOSE(MMULT((T(OFFSET(INDIRECT("'"&TRANSPOSE(Sheet1!$C$2:$E$2)&"'!D:D"),,COLUMN(INDIRECT("D:D"))-15,,1))="Bonus")*(COLUMN(INDIRECT("D:D"))-15+1),TRANSPOSE(COLUMN(INDIRECT("D:D"))^0)))

 

[Domenic]

The formula should be as follows...

=TRANSPOSE(MMULT((T(OFFSET(INDIRECT("'"&TRANSPOSE(Sheet1!$C$2:$E$2)&"'!D1:X1"),,COLUMN(INDIRECT("D:X"))-COLUMN(INDIRECT("D:D")),,1))="Bonus")*(COLUMN(INDIRECT("D:X"))-COLUMN(INDIRECT("D:D"))+1),TRANSPOSE(COLUMN(INDIRECT("D:X"))^0)))