Thank you so much!! This worked really well and I'm well on my way now to
finishing this project!
Jana
"Biff" wrote:
P.S.
Also, ultimately, I would like these formulas to
calculate in another worksheet altogether, but that is
way above my head! Can all this actually happen in the
same formula??
I forgot about this part!
The simple answer is YES!
Assume your data sheet is sheet1 and you want the formula
on sheet2. You also use the reference cells A102, A103 and
A105 on sheet2:
=SUMPRODUCT(--(Sheet1!K1:K1000=A102),--(Sheet1!
K1:K1000<=A103),--(TRIM(Sheet1!F1:F1000)=TRIM
(A105)),Sheet1!G1:G1000)
Biff
-----Original Message-----
Hi!
This is the extra variable I need to count by.
Your formula is a conditional SUM, not a COUNT.
As written, there is nothing wrong with the formula so
you
need to check your data. Make sure that A105 does not
contain any "extra" spaces. You could also make sure
there
are no "extra" spaces in column F.
Try this formula:
=SUMPRODUCT(--(K1:K1000=A102),--(K1:K1000<=A103),--(TRIM
(F1:F1000)=TRIM(A105)),G1:G1000)
Biff
-----Original Message-----
I have used the previous posts here to answer most of my
questions today, but
was just advised I need to add another variable into my
formula... I've tried
to adjust it and it doesn't work and I'm now at a loss..
Can someone help!?
Currently I have:
=SUMPRODUCT(--(K1:K1000=A102),--(K1:K1000<=A103),--
(F1:F1000=A105),G1:G1000)
Column K contains different dates
Column G contains a random number between 1 and 1000
(the
# of participants)
Column F contains one of six different options
(Teambuilding, Coaching...
etc) (picked from a Validated List)
A102 is January 1/05
A103 is January 31/05
A105 contains the word 'Teambuilding'
The answer comes out to be "0"
My original formula worked, but I didn't include "--
(F1:F1000=A105)". This
is the extra variable I need to count by.
Also, ultimately, I would like these formulas to
calculate in another
worksheet altogether, but that is way above my head!
Can
all this actually
happen in the same formula??
Thanks!
Jayded
.
.
|