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]

Hi Domenic,


The formula works fantastic.

But the only thing is that some of the worksheets has the #DIV/0! errors. Can we ignore those type of errors. Maybe with the ISNUMBER Function?

 

[Fin Fang Foom]

I figured it out.


=SUM(IF(ArrayA,IF(ISNUMBER(ArrayA),IF(ISNUMBER(ArrayB),ArrayB))))


Thank You so much for this solution. This really going to save me time when I go back to work on Monday.

Thank You!

 

[Domenic]

I figured it out.


=SUM(IF(ArrayA,IF(ISNUMBER(ArrayA),IF(ISNUMBER(ArrayB),ArrayB))))

Good show! Although, it doesn't look like ArrayA (Column C) should contain any errors. As far as I can see it contains the clock number and no division takes place. If this is the case, then the following should suffice...

=SUM(IF(ArrayA,IF(ISNUMBER(ArrayB),ArrayB)))

Thank You so much for this solution. This really going to save me time when I go back to work on Monday.

Thank You!

You're very welcome! Glad I could help!

Cheers!

 

[Fin Fang Foom]

=SUM(IF(ArrayA,IF(ISNUMBER(ArrayB),ArrayB)))

Thank You so much for this solution. This really going to save me time when I go back to work on Monday.

Thank You!

You're very welcome! Glad I could help!

Cheers!

Once again Thank You!

 

[Fin Fang Foom]

Hi Domenic,


Is there a way we could modify this define name range formula below? Right now when I create my weekly and monthly reports I need to change the column ranges manually in the formula below and it's various at times ranges from C thru U or D thru AA and so forth. Also there is that 29 limit could be use in that formula. There are some workbooks I need a range from C thru AG. If this could be done that will be great.


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

 

[Domenic]

Hi Fin,

Now that I've had another look at this, here's what I should have given you in the first place...

1) Delete 'Col', since you're not going to need it.

2) Change the reference for 'Pos' to the following...

=TRANSPOSE(MMULT((T(OFFSET(INDIRECT("'"&TRANSPOSE(Sheet1!$C$2:$D$2)&"'!C1:AG1"),,COLUMN(INDIRECT("C:AG"))-3,,1))="Bonus")*(COLUMN(INDIRECT("C:AG"))-3),TRANSPOSE(COLUMN(INDIRECT("C:AG"))^0)))

3) Change the reference for ArrayB to the following...

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

I'm assuming that you have or will change the column reference C3:C15 for ArrayA, accordingly.

Hope this helps!

 

[Fin Fang Foom]

Thanks Domenic thats allot easier to type. Thank You!!