Home |
Search |
Today's Posts |
#1
|
|||
|
|||
SUMIF in multiple columns based on other criteria in Excel?
I'm trying to sum the number in one column based on criteria in another
column, but there are also multiple columns on the spreadsheet. 1 5 2 5 1.5 7 2 7 If the second number is a 5, I want the sum of the first numbers. Likewise for the 7. Thanks, Scott |
#2
|
|||
|
|||
Hi Scott
not actually sure what the criteria is here ... or what you're after do you want the sum all the values together that are to the left of a 5 or a 7 or do you want to sum the first column if the second column is either a 5 or 7? or do you want to sum the numbers in column A if B is a 5, in C if D is a 5, E if F is a 7 and G if H is a 7 -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Scott Powell" <Scott wrote in message ... I'm trying to sum the number in one column based on criteria in another column, but there are also multiple columns on the spreadsheet. 1 5 2 5 1.5 7 2 7 If the second number is a 5, I want the sum of the first numbers. Likewise for the 7. Thanks, Scott |
#3
|
|||
|
|||
I'm not sure if this is what you're looking for, but try the following...
For data contained in one row: =SUMIF(B1:H1,5,A1:G1) For data contained in several rows (10 rows in this example): =SUMIF(B1:H10,5,A1:G10) Hope this helps! In article , Scott Powell <Scott wrote: I'm trying to sum the number in one column based on criteria in another column, but there are also multiple columns on the spreadsheet. 1 5 2 5 1.5 7 2 7 If the second number is a 5, I want the sum of the first numbers. Likewise for the 7. Thanks, Scott |
#5
|
|||
|
|||
I guess the question was a LITTLE confusing.
Here is the information hopefully a little better explained. A B C D E F G H I J K L M N 1 5 1 7 1 5 1 7 1 7 1 7 1 5 If B,D,F,H,J,L,N is a 5, I want the sum of the row values in A,C,E,G,I,K,M. If B,D,F,H,J,L,N is a 7, then I want to the sum of the row values in A,C,E,G,I,K,M. I assume each of these would have to be done in separate cells. The data I have has more than 2 columns for each occurence, so the criteria is the 5 or 7, and depending on the criteria I want to sum the corresponding values. I hope that clears up what I'm trying to do a little better. It's not as easy to explain as I thought it would be. Thank you. "JulieD" wrote: Hi Scott not actually sure what the criteria is here ... or what you're after do you want the sum all the values together that are to the left of a 5 or a 7 or do you want to sum the first column if the second column is either a 5 or 7? or do you want to sum the numbers in column A if B is a 5, in C if D is a 5, E if F is a 7 and G if H is a 7 -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Scott Powell" <Scott wrote in message ... I'm trying to sum the number in one column based on criteria in another column, but there are also multiple columns on the spreadsheet. 1 5 2 5 1.5 7 2 7 If the second number is a 5, I want the sum of the first numbers. Likewise for the 7. Thanks, Scott |
#6
|
|||
|
|||
Try...
For 5 as your criterion... =SUMPRODUCT(--(MOD(COLUMN(B1:N1)-COLUMN(B1),2)=0),--(B1:N1=5),A1:M1) For 7 as your criterion... =SUMPRODUCT(--(MOD(COLUMN(B1:N1)-COLUMN(B1),2)=0),--(B1:N1=7),A1:M1) Hope this helps! In article , Scott Powell wrote: I guess the question was a LITTLE confusing. Here is the information hopefully a little better explained. A B C D E F G H I J K L M N 1 5 1 7 1 5 1 7 1 7 1 7 1 5 If B,D,F,H,J,L,N is a 5, I want the sum of the row values in A,C,E,G,I,K,M. If B,D,F,H,J,L,N is a 7, then I want to the sum of the row values in A,C,E,G,I,K,M. I assume each of these would have to be done in separate cells. The data I have has more than 2 columns for each occurence, so the criteria is the 5 or 7, and depending on the criteria I want to sum the corresponding values. I hope that clears up what I'm trying to do a little better. It's not as easy to explain as I thought it would be. Thank you. "JulieD" wrote: Hi Scott not actually sure what the criteria is here ... or what you're after do you want the sum all the values together that are to the left of a 5 or a 7 or do you want to sum the first column if the second column is either a 5 or 7? or do you want to sum the numbers in column A if B is a 5, in C if D is a 5, E if F is a 7 and G if H is a 7 -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Scott Powell" <Scott wrote in message ... I'm trying to sum the number in one column based on criteria in another column, but there are also multiple columns on the spreadsheet. 1 5 2 5 1.5 7 2 7 If the second number is a 5, I want the sum of the first numbers. Likewise for the 7. Thanks, Scott |
#7
|
|||
|
|||
Hi Scott
still not 100% clear on what you're after but .... how about this for the 5s =SUM(IF(B2=5,A2,0),IF(D2=5,C2,0),IF(F2=5,E2,0),IF( H2=5,G2,0),IF(J2=5,I2,0),IF(L2=5,K2,0),IF(N2=5,M2, 0)) subsitute 7 in the formula to add up the 7s (hopefully someone can come up with a neater solution) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Scott Powell" wrote in message ... I guess the question was a LITTLE confusing. Here is the information hopefully a little better explained. A B C D E F G H I J K L M N 1 5 1 7 1 5 1 7 1 7 1 7 1 5 If B,D,F,H,J,L,N is a 5, I want the sum of the row values in A,C,E,G,I,K,M. If B,D,F,H,J,L,N is a 7, then I want to the sum of the row values in A,C,E,G,I,K,M. I assume each of these would have to be done in separate cells. The data I have has more than 2 columns for each occurence, so the criteria is the 5 or 7, and depending on the criteria I want to sum the corresponding values. I hope that clears up what I'm trying to do a little better. It's not as easy to explain as I thought it would be. Thank you. "JulieD" wrote: Hi Scott not actually sure what the criteria is here ... or what you're after do you want the sum all the values together that are to the left of a 5 or a 7 or do you want to sum the first column if the second column is either a 5 or 7? or do you want to sum the numbers in column A if B is a 5, in C if D is a 5, E if F is a 7 and G if H is a 7 -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Scott Powell" <Scott wrote in message ... I'm trying to sum the number in one column based on criteria in another column, but there are also multiple columns on the spreadsheet. 1 5 2 5 1.5 7 2 7 If the second number is a 5, I want the sum of the first numbers. Likewise for the 7. Thanks, Scott |
#8
|
|||
|
|||
Worked great.
Thank you so much. Scott "JulieD" wrote: Hi Scott still not 100% clear on what you're after but .... how about this for the 5s =SUM(IF(B2=5,A2,0),IF(D2=5,C2,0),IF(F2=5,E2,0),IF( H2=5,G2,0),IF(J2=5,I2,0),IF(L2=5,K2,0),IF(N2=5,M2, 0)) subsitute 7 in the formula to add up the 7s (hopefully someone can come up with a neater solution) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Scott Powell" wrote in message ... I guess the question was a LITTLE confusing. Here is the information hopefully a little better explained. A B C D E F G H I J K L M N 1 5 1 7 1 5 1 7 1 7 1 7 1 5 If B,D,F,H,J,L,N is a 5, I want the sum of the row values in A,C,E,G,I,K,M. If B,D,F,H,J,L,N is a 7, then I want to the sum of the row values in A,C,E,G,I,K,M. I assume each of these would have to be done in separate cells. The data I have has more than 2 columns for each occurence, so the criteria is the 5 or 7, and depending on the criteria I want to sum the corresponding values. I hope that clears up what I'm trying to do a little better. It's not as easy to explain as I thought it would be. Thank you. "JulieD" wrote: Hi Scott not actually sure what the criteria is here ... or what you're after do you want the sum all the values together that are to the left of a 5 or a 7 or do you want to sum the first column if the second column is either a 5 or 7? or do you want to sum the numbers in column A if B is a 5, in C if D is a 5, E if F is a 7 and G if H is a 7 -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Scott Powell" <Scott wrote in message ... I'm trying to sum the number in one column based on criteria in another column, but there are also multiple columns on the spreadsheet. 1 5 2 5 1.5 7 2 7 If the second number is a 5, I want the sum of the first numbers. Likewise for the 7. Thanks, Scott |
#9
|
|||
|
|||
Hi Scott
you're welcome, but Dominic's solution was a much neater & probably better solution than mine - so you might like to take another look at it Cheers JulieD "Scott Powell" wrote in message ... Worked great. Thank you so much. Scott "JulieD" wrote: Hi Scott still not 100% clear on what you're after but .... how about this for the 5s =SUM(IF(B2=5,A2,0),IF(D2=5,C2,0),IF(F2=5,E2,0),IF( H2=5,G2,0),IF(J2=5,I2,0),IF(L2=5,K2,0),IF(N2=5,M2, 0)) subsitute 7 in the formula to add up the 7s (hopefully someone can come up with a neater solution) -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Scott Powell" wrote in message ... I guess the question was a LITTLE confusing. Here is the information hopefully a little better explained. A B C D E F G H I J K L M N 1 5 1 7 1 5 1 7 1 7 1 7 1 5 If B,D,F,H,J,L,N is a 5, I want the sum of the row values in A,C,E,G,I,K,M. If B,D,F,H,J,L,N is a 7, then I want to the sum of the row values in A,C,E,G,I,K,M. I assume each of these would have to be done in separate cells. The data I have has more than 2 columns for each occurence, so the criteria is the 5 or 7, and depending on the criteria I want to sum the corresponding values. I hope that clears up what I'm trying to do a little better. It's not as easy to explain as I thought it would be. Thank you. "JulieD" wrote: Hi Scott not actually sure what the criteria is here ... or what you're after do you want the sum all the values together that are to the left of a 5 or a 7 or do you want to sum the first column if the second column is either a 5 or 7? or do you want to sum the numbers in column A if B is a 5, in C if D is a 5, E if F is a 7 and G if H is a 7 -- Cheers JulieD check out www.hcts.net.au/tipsandtricks.htm ....well i'm working on it anyway "Scott Powell" <Scott wrote in message ... I'm trying to sum the number in one column based on criteria in another column, but there are also multiple columns on the spreadsheet. 1 5 2 5 1.5 7 2 7 If the second number is a 5, I want the sum of the first numbers. Likewise for the 7. Thanks, Scott |
#10
|
|||
|
|||
Hi Julie,
As far as I know, the only advantage in using my formula is if the range needs to be expanded to include a large number of cells. Otherwise, your formula is fine. Nothing's wrong with it. :) Cheers! In article , "JulieD" wrote: Hi Scott you're welcome, but Dominic's solution was a much neater & probably better solution than mine - so you might like to take another look at it Cheers JulieD |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I use the sumif if I have multiple criteria (i.e. greater . | Excel Worksheet Functions | |||
Sumif over multiple columns | Excel Worksheet Functions | |||
SumIf Function using multiple criteria | Excel Worksheet Functions | |||
How do I sum (like sumif) but predicated on multiple criteria, in. | Excel Worksheet Functions | |||
How do I ask for multiple criteria when creating a "sumif" formul. | Excel Worksheet Functions |