Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SumProduct/CountIf dilemna + Date Range
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 |
#2
|
|||
|
|||
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 . |
#3
|
|||
|
|||
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) "Jayded542" wrote in 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 |
#4
|
|||
|
|||
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 . . |
#5
|
|||
|
|||
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). Jayded542 wrote: 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 |
#6
|
|||
|
|||
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 . . |
#7
|
|||
|
|||
Thanks to everyone that helped me! This made my life so much easier!!
Jana "Jayded542" wrote: 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 |
#8
|
|||
|
|||
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 -- Message posted via http://www.officekb.com |
#9
|
|||
|
|||
You could add a criteria
=SUMPRODUCT(--ISNUMBER((A2:A20)),B2:B20) will disregard the errors in A and only count the numbers -- Regards, Peo Sjoblom "Allen Way via OfficeKB.com" wrote in message ... 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 -- Message posted via http://www.officekb.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
adding occurrences for date range | Excel Discussion (Misc queries) | |||
adding occurrences for date range | Excel Discussion (Misc queries) | |||
Finding dates within a date range | Excel Worksheet Functions | |||
SUMPRODUCT with date range question | Excel Discussion (Misc queries) | |||
How can I sum information in a list with a date range? | Excel Worksheet Functions |