SumProduct/CountIf dilemna + Date Range

[Guest]

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

 

[Biff]

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

 

[Bob Phillips]

Jayded,

The formula does work, I just tried it.

Check that the text strings are the same, same spelling, no extra spaces,
etc.

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[Biff]

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

 

[Aladin Akyurek]

Run the TrimAll macro (try Google to find it) on the F-range to remove
extraneous non-printable chars including spaces. Another way to set up
your formula is:

=SUMPRODUCT(--(DATE(YEAR(K1:K1000),MONTH(K1:K1000,1)=A102),--(F1:F1000=A105),G1:G1000)

where A102 is set to the first day date of the month/year of interest
like in: 1/1/05 (that is, the first of January 2005).

 

[Guest]

Thank you so much!! This worked really well and I'm well on my way now to
finishing this project!
Jana

 

[Guest]

Thanks to everyone that helped me! This made my life so much easier!!
Jana

 

[Allen Way via OfficeKB.com]

This was very helpful to me. However, I have a data range that contains
many errors (5 / NA = #VALUE!). How can I get it to skip those cells with
the errors? I want to process those separately but the sumproduct function
does not want to handle them either way.

--Allen

 

[Peo Sjoblom]

You could add a criteria

=SUMPRODUCT(--ISNUMBER((A2:A20)),B2:B20)

will disregard the errors in A and only count the numbers