Range of Cells

F

Frank Kabel

Hi
try changing the SUMPRODUCT formulas to

SUMPRODUCT(--
('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C17:L29="Care"),--
(MOD(ROW
('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C17:L29="Care")-17,3)
=0))+......


-----Original Message-----
Here is the current formula:

=+SUMPRODUCT
(--
Click to expand...
('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT
(--
Click to expand...
'\\ntphg001v\common_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Williams, Melody.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT(--
Click to expand...
('\\ntphg001v\common_b\QA_Team_Specialists\Dale''s_Team_B_&
_C\[Shepherd.xls]July # of Obs'!C7:L7="Care"))+SUMPRODUCT(-
-('[Moore.xls]July # of Obs'!C7:L7="Care"))
What I need to change is the range of cells, currently they are C7:L7

What I need to include, on the same line, for each of the file references, is:

C17:L17
C20:L20
C23:L23
C26:L26
C29:L29

I tried: C17:L17+C20:L20+C23:L23+C26:L26+C29:L29
I tried: (C17:L17)+(C20:L20)+(C23:L23)+(C26:L26)+ (C29:L29)
And: [C17:L17]+[C20:L20]+[C23:L23]+[C26:L26]+[C29:L29]

I am sure it is simple but I couldnt get it!
.
Click to expand...
 
F

Frank Kabel

Hi
one workaround: split the formula on several cells and add
these helper cells. You may hide them afterwards
-----Original Message-----
The only problem with that is the need to keep the
Click to expand...
character limit down...and since I have ALOT of different
files that I am using this for/referencing....
This is why I was trying to just name the cells that I
Click to expand...
wanted to add up.....Could I just list each cell, adding
them up in the formula, saving precious character space?
Frank Kabel said:
Hi
try changing the SUMPRODUCT formulas to

SUMPRODUCT(--
('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C17:L29="Care"),--
(MOD(ROW
('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C17:L29="Care")- 17,3)
=0))+......


-----Original Message-----
Here is the current formula:

=+SUMPRODUCT
(--
Click to expand...
('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT
(--
Click to expand...
'\\ntphg001v\common_b\QA_Team_Specialists\Dale''s_Team_B _&_
C\[Williams, Melody.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT(--
Click to expand...
('\\ntphg001v\common_b\QA_Team_Specialists\Dale''s_Team_B_&
_C\[Shepherd.xls]July # of Obs'!C7:L7="Care")) +SUMPRODUCT(-
-('[Moore.xls]July # of Obs'!C7:L7="Care"))
What I need to change is the range of cells, currently they are C7:L7

What I need to include, on the same line, for each of
Click to expand...
the
file references, is:
C17:L17
C20:L20
C23:L23
C26:L26
C29:L29

I tried: C17:L17+C20:L20+C23:L23+C26:L26+C29:L29
I tried: (C17:L17)+(C20:L20)+(C23:L23)+(C26:L26)+ (C29:L29)
And: [C17:L17]+[C20:L20]+[C23:L23]+[C26:L26]+ [C29:L29]

I am sure it is simple but I couldnt get it!
.
Click to expand...
Click to expand...
.
Click to expand...
 
B

BenJAMMIN

What if I just want to use the SUM feature from across mutliple workbooks, how can I add the different ranges from each workbook?

Frank Kabel said:
Hi
one workaround: split the formula on several cells and add
these helper cells. You may hide them afterwards
-----Original Message-----
The only problem with that is the need to keep the
Click to expand...
character limit down...and since I have ALOT of different
files that I am using this for/referencing....
This is why I was trying to just name the cells that I
Click to expand...
wanted to add up.....Could I just list each cell, adding
them up in the formula, saving precious character space?
Frank Kabel said:
Hi
try changing the SUMPRODUCT formulas to

SUMPRODUCT(--
('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C17:L29="Care"),--
(MOD(ROW
('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C17:L29="Care")- 17,3)
=0))+......



-----Original Message-----
Here is the current formula:

=+SUMPRODUCT
(--
('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT
(--
'\\ntphg001v\common_b\QA_Team_Specialists\Dale''s_Team_B _&_
C\[Williams, Melody.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT(--
('\\ntphg001v\common_b\QA_Team_Specialists\Dale''s_Team_B_&
_C\[Shepherd.xls]July # of Obs'!C7:L7="Care")) +SUMPRODUCT(-
-('[Moore.xls]July # of Obs'!C7:L7="Care"))


What I need to change is the range of cells, currently
they are C7:L7

What I need to include, on the same line, for each of the
file references, is:

C17:L17
C20:L20
C23:L23
C26:L26
C29:L29

I tried: C17:L17+C20:L20+C23:L23+C26:L26+C29:L29
I tried: (C17:L17)+(C20:L20)+(C23:L23)+(C26:L26)+
(C29:L29)
And: [C17:L17]+[C20:L20]+[C23:L23]+[C26:L26]+ [C29:L29]

I am sure it is simple but I couldnt get it!
.
Click to expand...
.
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi
if you want to sum you can use something like
=SUM(range1,range2,range3,....)

--
Regards
Frank Kabel
Frankfurt, Germany

What if I just want to use the SUM feature from across mutliple
workbooks, how can I add the different ranges from each workbook?

Frank Kabel said:
Hi
one workaround: split the formula on several cells and add
these helper cells. You may hide them afterwards
-----Original Message-----
The only problem with that is the need to keep the
Click to expand...
character limit down...and since I have ALOT of different
files that I am using this for/referencing....
This is why I was trying to just name the cells that I
Click to expand...
wanted to add up.....Could I just list each cell, adding
them up in the formula, saving precious character space?
:

Hi
try changing the SUMPRODUCT formulas to

SUMPRODUCT(--
('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C17:L29="Care"),--
(MOD(ROW
('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C17:L29="Care")- 17,3)
=0))+......



-----Original Message-----
Here is the current formula:

=+SUMPRODUCT
(--
('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT
(--
'\\ntphg001v\common_b\QA_Team_Specialists\Dale''s_Team_B _&_
C\[Williams, Melody.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT(--
('\\ntphg001v\common_b\QA_Team_Specialists\Dale''s_Team_B_&
_C\[Shepherd.xls]July # of Obs'!C7:L7="Care")) +SUMPRODUCT(-
-('[Moore.xls]July # of Obs'!C7:L7="Care"))


What I need to change is the range of cells, currently
they are C7:L7

What I need to include, on the same line, for each of the file
references, is:

C17:L17
C20:L20
C23:L23
C26:L26
C29:L29

I tried: C17:L17+C20:L20+C23:L23+C26:L26+C29:L29
I tried: (C17:L17)+(C20:L20)+(C23:L23)+(C26:L26)+
(C29:L29)
And: [C17:L17]+[C20:L20]+[C23:L23]+[C26:L26]+ [C29:L29]

I am sure it is simple but I couldnt get it!
.


.
Click to expand...
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi
also in this case you have to use the filename for EACH cell reference.
But for your example try the following formula:
=SUMPRODUCT(--(MOD(ROW('\\ntphg001v2\common_b\QA_Team_Specialists\Dale'
's_Team_B_&_C\[Floyd.xls]July # of
Obs'!C17:L29)-17,3)=0),--(MOD(COLUMN('\\ntphg001v2\common_b\QA_Team_Spe
cialists\Dale''s_Team_B_&_C\[Floyd.xls]July # of
Obs'!C17:L29),3)=0),'\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_
Team_B_&_C\[Floyd.xls]July # of Obs'!C17:L29)



--
Regards
Frank Kabel
Frankfurt, Germany

That will work...now I just need it to reference the different
workbooks....I tried this formula but no dice!

=SUM(('\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_Team_B_&_C\[Fl
oyd.xls]July
# of
Obs'!C17,C20,C23,C26,C29,F17,F20,F23,F26,F29,I17,I20,I23,I26,I29,L17,L2
0,L23,L26,L29))


Frank Kabel said:
Hi
if you want to sum you can use something like
=SUM(range1,range2,range3,....)

--
Regards
Frank Kabel
Frankfurt, Germany

What if I just want to use the SUM feature from across mutliple
workbooks, how can I add the different ranges from each workbook?

:

Hi
one workaround: split the formula on several cells and add
these helper cells. You may hide them afterwards

-----Original Message-----
The only problem with that is the need to keep the
character limit down...and since I have ALOT of different
files that I am using this for/referencing....

This is why I was trying to just name the cells that I
wanted to add up.....Could I just list each cell, adding
them up in the formula, saving precious character space?

:

Hi
try changing the SUMPRODUCT formulas to

SUMPRODUCT(--

('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C17:L29="Care"),--
(MOD(ROW

('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C17:L29="Care")-
17,3)
=0))+......



-----Original Message-----
Here is the current formula:

=+SUMPRODUCT
(--

('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT
(--
'\\ntphg001v\common_b\QA_Team_Specialists\Dale''s_Team_B
_&_
C\[Williams, Melody.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT(--

('\\ntphg001v\common_b\QA_Team_Specialists\Dale''s_Team_B_&
_C\[Shepherd.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT(-
-('[Moore.xls]July # of Obs'!C7:L7="Care"))


What I need to change is the range of cells, currently
they are C7:L7

What I need to include, on the same line, for each of the file
references, is:

C17:L17
C20:L20
C23:L23
C26:L26
C29:L29

I tried: C17:L17+C20:L20+C23:L23+C26:L26+C29:L29
I tried: (C17:L17)+(C20:L20)+(C23:L23)+(C26:L26)+
(C29:L29)
And: [C17:L17]+[C20:L20]+[C23:L23]+[C26:L26]+
[C29:L29]

I am sure it is simple but I couldnt get it!
.


.
Click to expand...
Click to expand...
Click to expand...
 
F

Frank Kabel

Hi
see my other post for using SUMPRODUCT in combination with ROW/COLUMN

--
Regards
Frank Kabel
Frankfurt, Germany

SUCCESS!!! BUT......

This formula works......
='\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_Team_B_&_C\[Floyd.x
ls]July
# of Obs'!$C$29:$D$29
'\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_Team_B_&_C\[Floyd.xl
s]July
# of Obs'!$F$29:$G$29
'\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_Team_B_&_C\[Floyd.xl
s]July
# of Obs'!$I$29:$J$29
'\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_Team_B_&_C\[Floyd.xl
s]July
# of Obs'!$L$29:$M$29
'\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_Team_B_&_C\[Floyd.xl
s]July
# of Obs'!$L$26:$M$26
'\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_Team_B_&_C\[Floyd.xl
s]July
# of Obs'!$I$26:$J$26
'\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_Team_B_&_C\[Floyd.xl
s]July
# of Obs'!$F$26:$G$26
'\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_Team_B_&_C\[Floyd.xl
s]July
# of Obs'!$C$26:$D$26
'\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_Team_B_&_C\[Floyd.xl
s]July
# of Obs'!$C$23:$D$23
'\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_Team_B_&_C\[Floyd.xl
s]July
# of Obs'!$F$23:$G$23


BUT...Look at how much size this takes up JUST to reference each of
these cells....so I am back to square one.....it works correctly but
there are SOOOO MANY workbooks that I need to use.......



BenJAMMIN said:
That will work...now I just need it to reference the different
workbooks....I tried this formula but no dice!
Click to expand...
=SUM(('\\ntphg001v2\common_b\QA_Team_Specialists\Dale''s_Team_B_&_C\[Fl
oyd.xls]July
# of
Click to expand...
Obs'!C17,C20,C23,C26,C29,F17,F20,F23,F26,F29,I17,I20,I23,I26,I29,L17,L2
0,L23,L26,L29))
Frank Kabel said:
Hi
if you want to sum you can use something like
=SUM(range1,range2,range3,....)

--
Regards
Frank Kabel
Frankfurt, Germany


BenJAMMIN wrote:
What if I just want to use the SUM feature from across mutliple
workbooks, how can I add the different ranges from each workbook?

:

Hi
one workaround: split the formula on several cells and add
these helper cells. You may hide them afterwards

-----Original Message-----
The only problem with that is the need to keep the
character limit down...and since I have ALOT of different
files that I am using this for/referencing....

This is why I was trying to just name the cells that I
wanted to add up.....Could I just list each cell, adding
them up in the formula, saving precious character space?

:

Hi
try changing the SUMPRODUCT formulas to

SUMPRODUCT(--

('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C17:L29="Care"),--
(MOD(ROW

('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C17:L29="Care")-
17,3)
=0))+......



-----Original Message-----
Here is the current formula:

=+SUMPRODUCT
(--

('\\ntphg001vcommon_b\QA_Team_Specialists\Dale''s_Team_B_&_
C\[Smith, Benjamin.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT
(--
'\\ntphg001v\common_b\QA_Team_Specialists\Dale''s_Team_B
_&_
C\[Williams, Melody.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT(--

('\\ntphg001v\common_b\QA_Team_Specialists\Dale''s_Team_B_&
_C\[Shepherd.xls]July # of Obs'!C7:L7="Care"))
+SUMPRODUCT(-
-('[Moore.xls]July # of Obs'!C7:L7="Care"))


What I need to change is the range of cells, currently
they are C7:L7

What I need to include, on the same line, for each of the file
references, is:

C17:L17
C20:L20
C23:L23
C26:L26
C29:L29

I tried: C17:L17+C20:L20+C23:L23+C26:L26+C29:L29
I tried: (C17:L17)+(C20:L20)+(C23:L23)+(C26:L26)+
(C29:L29)
And: [C17:L17]+[C20:L20]+[C23:L23]+[C26:L26]+
[C29:L29]

I am sure it is simple but I couldnt get it!
.


.
Click to expand...
Click to expand...
Click to expand...
 
Top