If 3 If's, then sum
in [Functions]
|
From: Ms-Exl-Learner on 16 Apr 2010 12:36 Not clear whether this is what you are looking for... 1st Formula:- This will generate the result when D3:D100 is not blank and when its matching other criteria =SUMPRODUCT((D3:D100<>"")*(J3:J100=10)*(G3:G100=90),H3:H100) 2nd Formula:- Or simply you can remove the D3:D100 criteria from the formula like the below =SUMPRODUCT((J3:J100=10)*(G3:G100=90),H3:H100) 3rd Formula:- This will generate the result when D3:D100 is having Numeric Values and when its matching other criteria. This will leave the Text entries in D3:D100 eventhough the other criteria's are matching. =SUMPRODUCT((ISNUMBER(D3:D100))*(J3:J100=10)*(G3:G100=90),H3:H100) Remember to Click Yes, if this post helps! -------------------- (Ms-Exl-Learner) -------------------- "Steve" wrote: > This works great. Thank you. > > However, I used a simple example. In reality,the J and G data had 5 > variables each, so I was going to create tables such as > row G J > 3 90 10 > 4 90 12 > 5 90 13 > 6 90 14 > 7 90 15 > 8 100 10 > 9 100 12 > 10 100 13 > 11 100 14 > 12 100 15, etc. > and use the formula not as specific numbers like 90 or 10, but use the cell > reference like G3 and J3. Where my problem comes is that the D column can be > various numbers up to appx. 200, so I'd like to be able to use in the formula > in place of D3:D100 =100, something that would use whatever is in that D > column. Is that possible ? > > Thanks again, > > Steve > > "Ms-Exl-Learner" wrote: > > > Try this... > > > > =SUMPRODUCT((D3:D100=100)*(J3:J100=10)*(G3:G100=90),H3:H100) > > > > Remember to Click Yes, if this post helps! > > > > -------------------- > > (Ms-Exl-Learner) > > -------------------- > > > > > > "Steve" wrote: > > > > > I need to add hours if 3 critera match. > > > In the below, if there is a 90 in the G column AND a 10 in the J Column, AND > > > a 100 in the D column, then add the H column. Being that this criterea is > > > found only in row 3 and row 6, I need the result to be 3 (H3 +H6). > > > > > > row D G H J > > > > > > 3 100 90 2 10 > > > 4 200 100 3 11 > > > 5 300 110 4 12 > > > 6 100 90 1 10 > > > 7 200 90 5 10 > > > 8 300 90 6 13 > > > > > > Thanks, > > > > > > Steve
From: Steve on 16 Apr 2010 13:40 They are all working as designed, but I'm having difficulty being clear. The D's are the variables that can be numerous, and can change daily. I guess what I'm trying to say is that, e.g., if there is 100 in the D:D then sum up those hours in H:H if they match 10 in J:J AND 90 in G:G. However, they may also be 150 in D:D. And if so, then sum up those hours in H:H if they match 10 in J:J AND 90 in G:G. And I need those numbers in D:D identified as to what they are. Thanks for your patience. "Ms-Exl-Learner" wrote: > Not clear whether this is what you are looking for... > > 1st Formula:- > This will generate the result when D3:D100 is not blank and when its > matching other criteria > =SUMPRODUCT((D3:D100<>"")*(J3:J100=10)*(G3:G100=90),H3:H100) > > 2nd Formula:- > Or simply you can remove the D3:D100 criteria from the formula like the below > =SUMPRODUCT((J3:J100=10)*(G3:G100=90),H3:H100) > > 3rd Formula:- > This will generate the result when D3:D100 is having Numeric Values and when > its matching other criteria. This will leave the Text entries in D3:D100 > eventhough the other criteria's are matching. > =SUMPRODUCT((ISNUMBER(D3:D100))*(J3:J100=10)*(G3:G100=90),H3:H100) > > Remember to Click Yes, if this post helps! > > -------------------- > (Ms-Exl-Learner) > -------------------- > > > "Steve" wrote: > > > This works great. Thank you. > > > > However, I used a simple example. In reality,the J and G data had 5 > > variables each, so I was going to create tables such as > > row G J > > 3 90 10 > > 4 90 12 > > 5 90 13 > > 6 90 14 > > 7 90 15 > > 8 100 10 > > 9 100 12 > > 10 100 13 > > 11 100 14 > > 12 100 15, etc. > > and use the formula not as specific numbers like 90 or 10, but use the cell > > reference like G3 and J3. Where my problem comes is that the D column can be > > various numbers up to appx. 200, so I'd like to be able to use in the formula > > in place of D3:D100 =100, something that would use whatever is in that D > > column. Is that possible ? > > > > Thanks again, > > > > Steve > > > > "Ms-Exl-Learner" wrote: > > > > > Try this... > > > > > > =SUMPRODUCT((D3:D100=100)*(J3:J100=10)*(G3:G100=90),H3:H100) > > > > > > Remember to Click Yes, if this post helps! > > > > > > -------------------- > > > (Ms-Exl-Learner) > > > -------------------- > > > > > > > > > "Steve" wrote: > > > > > > > I need to add hours if 3 critera match. > > > > In the below, if there is a 90 in the G column AND a 10 in the J Column, AND > > > > a 100 in the D column, then add the H column. Being that this criterea is > > > > found only in row 3 and row 6, I need the result to be 3 (H3 +H6). > > > > > > > > row D G H J > > > > > > > > 3 100 90 2 10 > > > > 4 200 100 3 11 > > > > 5 300 110 4 12 > > > > 6 100 90 1 10 > > > > 7 200 90 5 10 > > > > 8 300 90 6 13 > > > > > > > > Thanks, > > > > > > > > Steve
From: Mitch on 16 Apr 2010 14:03 Hi Steve, If that is the case, you can use SUMIFS function. D G H J GG JJ 100 90 2 10 90 10 200 100 3 11 300 110 4 12 100 90 1 10 200 90 5 10 300 90 6 13 =SUMIFS($H3:$H100,$G3:$G100,$GG$3,$J3:$J100,$JJ$3) Place the result somewhere you will not paste over. Hope this helps. Press Yes if it does. Thanks, Mitch "Steve" wrote: > They are all working as designed, but I'm having difficulty being clear. > The D's are the variables that can be numerous, and can change daily. > I guess what I'm trying to say is that, e.g., if there is 100 in the D:D > then sum up those hours in H:H if they match 10 in J:J AND 90 in G:G. > However, they may also be 150 in D:D. And if so, then sum up those hours in > H:H if they match 10 in J:J AND 90 in G:G. And I need those numbers in D:D > identified as to what they are. > > Thanks for your patience. > > "Ms-Exl-Learner" wrote: > > > Not clear whether this is what you are looking for... > > > > 1st Formula:- > > This will generate the result when D3:D100 is not blank and when its > > matching other criteria > > =SUMPRODUCT((D3:D100<>"")*(J3:J100=10)*(G3:G100=90),H3:H100) > > > > 2nd Formula:- > > Or simply you can remove the D3:D100 criteria from the formula like the below > > =SUMPRODUCT((J3:J100=10)*(G3:G100=90),H3:H100) > > > > 3rd Formula:- > > This will generate the result when D3:D100 is having Numeric Values and when > > its matching other criteria. This will leave the Text entries in D3:D100 > > eventhough the other criteria's are matching. > > =SUMPRODUCT((ISNUMBER(D3:D100))*(J3:J100=10)*(G3:G100=90),H3:H100) > > > > Remember to Click Yes, if this post helps! > > > > -------------------- > > (Ms-Exl-Learner) > > -------------------- > > > > > > "Steve" wrote: > > > > > This works great. Thank you. > > > > > > However, I used a simple example. In reality,the J and G data had 5 > > > variables each, so I was going to create tables such as > > > row G J > > > 3 90 10 > > > 4 90 12 > > > 5 90 13 > > > 6 90 14 > > > 7 90 15 > > > 8 100 10 > > > 9 100 12 > > > 10 100 13 > > > 11 100 14 > > > 12 100 15, etc. > > > and use the formula not as specific numbers like 90 or 10, but use the cell > > > reference like G3 and J3. Where my problem comes is that the D column can be > > > various numbers up to appx. 200, so I'd like to be able to use in the formula > > > in place of D3:D100 =100, something that would use whatever is in that D > > > column. Is that possible ? > > > > > > Thanks again, > > > > > > Steve > > > > > > "Ms-Exl-Learner" wrote: > > > > > > > Try this... > > > > > > > > =SUMPRODUCT((D3:D100=100)*(J3:J100=10)*(G3:G100=90),H3:H100) > > > > > > > > Remember to Click Yes, if this post helps! > > > > > > > > -------------------- > > > > (Ms-Exl-Learner) > > > > -------------------- > > > > > > > > > > > > "Steve" wrote: > > > > > > > > > I need to add hours if 3 critera match. > > > > > In the below, if there is a 90 in the G column AND a 10 in the J Column, AND > > > > > a 100 in the D column, then add the H column. Being that this criterea is > > > > > found only in row 3 and row 6, I need the result to be 3 (H3 +H6). > > > > > > > > > > row D G H J > > > > > > > > > > 3 100 90 2 10 > > > > > 4 200 100 3 11 > > > > > 5 300 110 4 12 > > > > > 6 100 90 1 10 > > > > > 7 200 90 5 10 > > > > > 8 300 90 6 13 > > > > > > > > > > Thanks, > > > > > > > > > > Steve
From: Mitch on 16 Apr 2010 14:12 Steve, From my latest answer, you could also extend the two sumif criterias into a arange like $GG3:$GG100 and $JJ3:$JJ100. "Mitch" wrote: > Hi Steve, > > If that is the case, you can use SUMIFS function. > > > D G H J GG JJ > 100 90 2 10 90 10 > 200 100 3 11 > 300 110 4 12 > 100 90 1 10 > 200 90 5 10 > 300 90 6 13 > > =SUMIFS($H3:$H100,$G3:$G100,$GG$3,$J3:$J100,$JJ$3) > > Place the result somewhere you will not paste over. > > Hope this helps. Press Yes if it does. > > Thanks, > > Mitch > > > "Steve" wrote: > > > They are all working as designed, but I'm having difficulty being clear. > > The D's are the variables that can be numerous, and can change daily. > > I guess what I'm trying to say is that, e.g., if there is 100 in the D:D > > then sum up those hours in H:H if they match 10 in J:J AND 90 in G:G. > > However, they may also be 150 in D:D. And if so, then sum up those hours in > > H:H if they match 10 in J:J AND 90 in G:G. And I need those numbers in D:D > > identified as to what they are. > > > > Thanks for your patience. > > > > "Ms-Exl-Learner" wrote: > > > > > Not clear whether this is what you are looking for... > > > > > > 1st Formula:- > > > This will generate the result when D3:D100 is not blank and when its > > > matching other criteria > > > =SUMPRODUCT((D3:D100<>"")*(J3:J100=10)*(G3:G100=90),H3:H100) > > > > > > 2nd Formula:- > > > Or simply you can remove the D3:D100 criteria from the formula like the below > > > =SUMPRODUCT((J3:J100=10)*(G3:G100=90),H3:H100) > > > > > > 3rd Formula:- > > > This will generate the result when D3:D100 is having Numeric Values and when > > > its matching other criteria. This will leave the Text entries in D3:D100 > > > eventhough the other criteria's are matching. > > > =SUMPRODUCT((ISNUMBER(D3:D100))*(J3:J100=10)*(G3:G100=90),H3:H100) > > > > > > Remember to Click Yes, if this post helps! > > > > > > -------------------- > > > (Ms-Exl-Learner) > > > -------------------- > > > > > > > > > "Steve" wrote: > > > > > > > This works great. Thank you. > > > > > > > > However, I used a simple example. In reality,the J and G data had 5 > > > > variables each, so I was going to create tables such as > > > > row G J > > > > 3 90 10 > > > > 4 90 12 > > > > 5 90 13 > > > > 6 90 14 > > > > 7 90 15 > > > > 8 100 10 > > > > 9 100 12 > > > > 10 100 13 > > > > 11 100 14 > > > > 12 100 15, etc. > > > > and use the formula not as specific numbers like 90 or 10, but use the cell > > > > reference like G3 and J3. Where my problem comes is that the D column can be > > > > various numbers up to appx. 200, so I'd like to be able to use in the formula > > > > in place of D3:D100 =100, something that would use whatever is in that D > > > > column. Is that possible ? > > > > > > > > Thanks again, > > > > > > > > Steve > > > > > > > > "Ms-Exl-Learner" wrote: > > > > > > > > > Try this... > > > > > > > > > > =SUMPRODUCT((D3:D100=100)*(J3:J100=10)*(G3:G100=90),H3:H100) > > > > > > > > > > Remember to Click Yes, if this post helps! > > > > > > > > > > -------------------- > > > > > (Ms-Exl-Learner) > > > > > -------------------- > > > > > > > > > > > > > > > "Steve" wrote: > > > > > > > > > > > I need to add hours if 3 critera match. > > > > > > In the below, if there is a 90 in the G column AND a 10 in the J Column, AND > > > > > > a 100 in the D column, then add the H column. Being that this criterea is > > > > > > found only in row 3 and row 6, I need the result to be 3 (H3 +H6). > > > > > > > > > > > > row D G H J > > > > > > > > > > > > 3 100 90 2 10 > > > > > > 4 200 100 3 11 > > > > > > 5 300 110 4 12 > > > > > > 6 100 90 1 10 > > > > > > 7 200 90 5 10 > > > > > > 8 300 90 6 13 > > > > > > > > > > > > Thanks, > > > > > > > > > > > > Steve
From: Steve on 16 Apr 2010 15:17 Sorry, I forgot to indicate that I'm using 2003, not 2007. "Mitch" wrote: > Hi Steve, > > If that is the case, you can use SUMIFS function. > > > D G H J GG JJ > 100 90 2 10 90 10 > 200 100 3 11 > 300 110 4 12 > 100 90 1 10 > 200 90 5 10 > 300 90 6 13 > > =SUMIFS($H3:$H100,$G3:$G100,$GG$3,$J3:$J100,$JJ$3) > > Place the result somewhere you will not paste over. > > Hope this helps. Press Yes if it does. > > Thanks, > > Mitch > > > "Steve" wrote: > > > They are all working as designed, but I'm having difficulty being clear. > > The D's are the variables that can be numerous, and can change daily. > > I guess what I'm trying to say is that, e.g., if there is 100 in the D:D > > then sum up those hours in H:H if they match 10 in J:J AND 90 in G:G. > > However, they may also be 150 in D:D. And if so, then sum up those hours in > > H:H if they match 10 in J:J AND 90 in G:G. And I need those numbers in D:D > > identified as to what they are. > > > > Thanks for your patience. > > > > "Ms-Exl-Learner" wrote: > > > > > Not clear whether this is what you are looking for... > > > > > > 1st Formula:- > > > This will generate the result when D3:D100 is not blank and when its > > > matching other criteria > > > =SUMPRODUCT((D3:D100<>"")*(J3:J100=10)*(G3:G100=90),H3:H100) > > > > > > 2nd Formula:- > > > Or simply you can remove the D3:D100 criteria from the formula like the below > > > =SUMPRODUCT((J3:J100=10)*(G3:G100=90),H3:H100) > > > > > > 3rd Formula:- > > > This will generate the result when D3:D100 is having Numeric Values and when > > > its matching other criteria. This will leave the Text entries in D3:D100 > > > eventhough the other criteria's are matching. > > > =SUMPRODUCT((ISNUMBER(D3:D100))*(J3:J100=10)*(G3:G100=90),H3:H100) > > > > > > Remember to Click Yes, if this post helps! > > > > > > -------------------- > > > (Ms-Exl-Learner) > > > -------------------- > > > > > > > > > "Steve" wrote: > > > > > > > This works great. Thank you. > > > > > > > > However, I used a simple example. In reality,the J and G data had 5 > > > > variables each, so I was going to create tables such as > > > > row G J > > > > 3 90 10 > > > > 4 90 12 > > > > 5 90 13 > > > > 6 90 14 > > > > 7 90 15 > > > > 8 100 10 > > > > 9 100 12 > > > > 10 100 13 > > > > 11 100 14 > > > > 12 100 15, etc. > > > > and use the formula not as specific numbers like 90 or 10, but use the cell > > > > reference like G3 and J3. Where my problem comes is that the D column can be > > > > various numbers up to appx. 200, so I'd like to be able to use in the formula > > > > in place of D3:D100 =100, something that would use whatever is in that D > > > > column. Is that possible ? > > > > > > > > Thanks again, > > > > > > > > Steve > > > > > > > > "Ms-Exl-Learner" wrote: > > > > > > > > > Try this... > > > > > > > > > > =SUMPRODUCT((D3:D100=100)*(J3:J100=10)*(G3:G100=90),H3:H100) > > > > > > > > > > Remember to Click Yes, if this post helps! > > > > > > > > > > -------------------- > > > > > (Ms-Exl-Learner) > > > > > -------------------- > > > > > > > > > > > > > > > "Steve" wrote: > > > > > > > > > > > I need to add hours if 3 critera match. > > > > > > In the below, if there is a 90 in the G column AND a 10 in the J Column, AND > > > > > > a 100 in the D column, then add the H column. Being that this criterea is > > > > > > found only in row 3 and row 6, I need the result to be 3 (H3 +H6). > > > > > > > > > > > > row D G H J > > > > > > > > > > > > 3 100 90 2 10 > > > > > > 4 200 100 3 11 > > > > > > 5 300 110 4 12 > > > > > > 6 100 90 1 10 > > > > > > 7 200 90 5 10 > > > > > > 8 300 90 6 13 > > > > > > > > > > > > Thanks, > > > > > > > > > > > > Steve
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: Dynamic Filtering...Need Help Next: Count if past today () - 30 days |