Couple of questions

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hey Frank it's me again, LOL I've got my bi monthly question for ya

Ok delima #1 I've got over 65536 users now so my data isn't fitting on one worksheet can you do complex functions such as

=SUMPRODUCT((DATA!C4:C14563="M")*(DATA!I4:I14563=77)*(DATA!O4:O14563=1),DATA!AJ4:AJ14563

on two work sheets say DATA and DATA

And second delima is that when i try to copy and paste a range of cells with the above functions it changes the cell #'s accordingly. I know I've seen before how to keep Excel from altering the code (at least I think I've seen it ) but could you refresh me where to find it in 2k

as always thanks for your expert advise

Justin
 
Hi Justin
you again :-)))

1. One way: Simple solution:
=SUMPRODUCT((DATA!C4:C14563="M")*(DATA!I4:I14563=77)*(DATA!O4:O14563=1)
,DATA!AJ4:AJ14563)+SUMPRODUCT((DATA1!C4:C14563="M")*(DATA1!I4:I14563=77
)*(DATA1!O4:O14563=1),DATA1!AJ4:AJ14563)

2. change the formula to
=SUMPRODUCT((DATA!$C$4:$C$14563="M")*(DATA!$I$4:$I$14563=77)*(DATA!$O$4
:$O$14563=1),DATA!$AJ$4:$AJ$14563)
have a look at 'absolute / relative cell references' in the Excel help
or see:
http://www.cpearson.com/excel/relative.htm
 
As always your a life saver Frank

Thanks agai

----- Frank Kabel wrote: ----

Hi Justi
you again :-))

1. One way: Simple solution
=SUMPRODUCT((DATA!C4:C14563="M")*(DATA!I4:I14563=77)*(DATA!O4:O14563=1
,DATA!AJ4:AJ14563)+SUMPRODUCT((DATA1!C4:C14563="M")*(DATA1!I4:I14563=7
)*(DATA1!O4:O14563=1),DATA1!AJ4:AJ14563

2. change the formula t
=SUMPRODUCT((DATA!$C$4:$C$14563="M")*(DATA!$I$4:$I$14563=77)*(DATA!$O$
:$O$14563=1),DATA!$AJ$4:$AJ$14563
have a look at 'absolute / relative cell references' in the Excel hel
or see
http://www.cpearson.com/excel/relative.ht






-
Regard
Frank Kabe
Frankfurt, German


Justin wrote
 
Back
Top