| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
sum of 1 column if info in another = specific text
How could i get the sum of a column that would not include in the total sum
any cells in another column that has certain info ex: if any corresponding cells in column A="spring" then do not include in total sum of cells in clumn b |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
Try...
=SUMIF(A1:A100,"<Spring",B1:B100) Hope this helps! In article , blackstar wrote: How could i get the sum of a column that would not include in the total sum any cells in another column that has certain info ex: if any corresponding cells in column A="spring" then do not include in total sum of cells in clumn b |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
=SUMIF(G16:G18,"<spring",H16:H18)
"blackstar" wrote: How could i get the sum of a column that would not include in the total sum any cells in another column that has certain info ex: if any corresponding cells in column A="spring" then do not include in total sum of cells in clumn b |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
nope, didn't work only gave me the sum of from ex h16:h18 regardless of info
in cells g16:g18 "Jonathan Cooper" wrote: =SUMIF(G16:G18,"<spring",H16:H18) "blackstar" wrote: How could i get the sum of a column that would not include in the total sum any cells in another column that has certain info ex: if any corresponding cells in column A="spring" then do not include in total sum of cells in clumn b |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
no dice, gives me a result of 0 every time. #'s are in column B "spring" is
in column A formula is in column B "Domenic" wrote: Try... =SUMIF(A1:A100,"<Spring",B1:B100) Hope this helps! In article , blackstar wrote: How could i get the sum of a column that would not include in the total sum any cells in another column that has certain info ex: if any corresponding cells in column A="spring" then do not include in total sum of cells in clumn b |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
See Domenic's response for a better example. this works for me. If it is
still not working for you, I suggest you check to see if you perhaps have an extra space or something in the cells that say spring. A trailing space can through you off. "blackstar" wrote: nope, didn't work only gave me the sum of from ex h16:h18 regardless of info in cells g16:g18 "Jonathan Cooper" wrote: =SUMIF(G16:G18,"<spring",H16:H18) "blackstar" wrote: How could i get the sum of a column that would not include in the total sum any cells in another column that has certain info ex: if any corresponding cells in column A="spring" then do not include in total sum of cells in clumn b |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
Just to be clear, do you want to sum Column B if the corresponding
values in Column A equals 'Spring' or if Column A doesn't equal 'Spring'.? In article , blackstar wrote: no dice, gives me a result of 0 every time. #'s are in column B "spring" is in column A formula is in column B "Domenic" wrote: Try... =SUMIF(A1:A100,"<Spring",B1:B100) Hope this helps! In article , blackstar wrote: How could i get the sum of a column that would not include in the total sum any cells in another column that has certain info ex: if any corresponding cells in column A="spring" then do not include in total sum of cells in clumn b |
|
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
so i've put in "spring" from a1:a4 and a5:a1000 has "summer" with #2 @ each
cell in column B... with the formula below it gives me the total of cell b1 to b1000 regardless of if its "spring" or "summer" in cells a1 to a1000 "blackstar" wrote: no dice, gives me a result of 0 every time. #'s are in column B "spring" is in column A formula is in column B "Domenic" wrote: Try... =SUMIF(A1:A100,"<Spring",B1:B100) Hope this helps! In article , blackstar wrote: How could i get the sum of a column that would not include in the total sum any cells in another column that has certain info ex: if any corresponding cells in column A="spring" then do not include in total sum of cells in clumn b |
|
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
i want the sum column B if column A equals spring. if it says anything other
than spring i don't want column B to take into account the values of B "Domenic" wrote: Just to be clear, do you want to sum Column B if the corresponding values in Column A equals 'Spring' or if Column A doesn't equal 'Spring'.? In article , blackstar wrote: no dice, gives me a result of 0 every time. #'s are in column B "spring" is in column A formula is in column B "Domenic" wrote: Try... =SUMIF(A1:A100,"<Spring",B1:B100) Hope this helps! In article , blackstar wrote: How could i get the sum of a column that would not include in the total sum any cells in another column that has certain info ex: if any corresponding cells in column A="spring" then do not include in total sum of cells in clumn b |
|
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
And, A1:A4, ONLY have the value spring typed in. No other text in those cells?
"blackstar" wrote: so i've put in "spring" from a1:a4 and a5:a1000 has "summer" with #2 @ each cell in column B... with the formula below it gives me the total of cell b1 to b1000 regardless of if its "spring" or "summer" in cells a1 to a1000 "blackstar" wrote: no dice, gives me a result of 0 every time. #'s are in column B "spring" is in column A formula is in column B "Domenic" wrote: Try... =SUMIF(A1:A100,"<Spring",B1:B100) Hope this helps! In article , blackstar wrote: How could i get the sum of a column that would not include in the total sum any cells in another column that has certain info ex: if any corresponding cells in column A="spring" then do not include in total sum of cells in clumn b |
|
#11
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
Ok. I know I'm late in the game here but I'll give it a shot. I think what you mean is that if the cells in column A contain the word spring then include the corresponding value in column B for your sum. I used the range A1:A14 and B1:B14. You can expand as needed. =SUMPRODUCT(--(A2:A14="spring"),(B2:B14)) If you mean to sum the values in B where the corresponding value in A does not equal spring than, =SUMPRODUCT(--(A2:A14<"spring"),(B2:B14)) -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=508160 |
|
#12
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
I think what Jonathan is getting at is that does the text in A1:A4 contain additional text like, The spring... spring up... or something like that. If so use wildcard characters before and after the work spring in the SUMIF formula. =SUMIF(A1:A14,"=*spring*",B1:B14) This will also take into account any preceeding or trailing spaces. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=508160 |
|
#13
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
in the specific example i gave you yes. but in the actual uses of this
function i'll need, some of those cells MIGHT have other values than spring, such as summer etc..so i need excel to know only to include the cells IF they say spring... and another cell would have a formula that would give me the sum only IF they say summer..etc etc "Jonathan Cooper" wrote: And, A1:A4, ONLY have the value spring typed in. No other text in those cells? "blackstar" wrote: so i've put in "spring" from a1:a4 and a5:a1000 has "summer" with #2 @ each cell in column B... with the formula below it gives me the total of cell b1 to b1000 regardless of if its "spring" or "summer" in cells a1 to a1000 "blackstar" wrote: no dice, gives me a result of 0 every time. #'s are in column B "spring" is in column A formula is in column B "Domenic" wrote: Try... =SUMIF(A1:A100,"<Spring",B1:B100) Hope this helps! In article , blackstar wrote: How could i get the sum of a column that would not include in the total sum any cells in another column that has certain info ex: if any corresponding cells in column A="spring" then do not include in total sum of cells in clumn b |
|
#14
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
you are correct. that's what I was getting at.
The sumif should take care of what is being asked. "SteveG" wrote: I think what Jonathan is getting at is that does the text in A1:A4 contain additional text like, The spring... spring up... or something like that. If so use wildcard characters before and after the work spring in the SUMIF formula. =SUMIF(A1:A14,"=*spring*",B1:B14) This will also take into account any preceeding or trailing spaces. HTH Steve -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=508160 |
|
#15
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
In that case, try...
=SUMIF(A1:A100,"Spring",B1:B100) Hope this helps! In article , blackstar wrote: i want the sum column B if column A equals spring. if it says anything other than spring i don't want column B to take into account the values of B "Domenic" wrote: Just to be clear, do you want to sum Column B if the corresponding values in Column A equals 'Spring' or if Column A doesn't equal 'Spring'.? In article , blackstar wrote: no dice, gives me a result of 0 every time. #'s are in column B "spring" is in column A formula is in column B "Domenic" wrote: Try... =SUMIF(A1:A100,"<Spring",B1:B100) Hope this helps! In article , blackstar wrote: How could i get the sum of a column that would not include in the total sum any cells in another column that has certain info ex: if any corresponding cells in column A="spring" then do not include in total sum of cells in clumn b |
|
#16
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
we have a winner!!
=sumproduct(--a2:a14="spring"),(b2:b14)) did the job!! thank you steveg "SteveG" wrote: Ok. I know I'm late in the game here but I'll give it a shot. I think what you mean is that if the cells in column A contain the word spring then include the corresponding value in column B for your sum. I used the range A1:A14 and B1:B14. You can expand as needed. =SUMPRODUCT(--(A2:A14="spring"),(B2:B14)) If you mean to sum the values in B where the corresponding value in A does not equal spring than, =SUMPRODUCT(--(A2:A14<"spring"),(B2:B14)) -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=508160 |
|
#17
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
now that that's fixed.. is it possible using that formula to have it readjust
itself if rows are added or removed. ex a2:a14 exists but if i add 5 people i want to formula to include a2:a21. same if i were to remove 2 people so i would only want it to look @ cell a2:12. i know this is easy to do manually but i need this setup so the dumbest person can use it easily. "blackstar" wrote: we have a winner!! =sumproduct(--a2:a14="spring"),(b2:b14)) did the job!! thank you steveg "SteveG" wrote: Ok. I know I'm late in the game here but I'll give it a shot. I think what you mean is that if the cells in column A contain the word spring then include the corresponding value in column B for your sum. I used the range A1:A14 and B1:B14. You can expand as needed. =SUMPRODUCT(--(A2:A14="spring"),(B2:B14)) If you mean to sum the values in B where the corresponding value in A does not equal spring than, =SUMPRODUCT(--(A2:A14<"spring"),(B2:B14)) -- SteveG ------------------------------------------------------------------------ SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571 View this thread: http://www.excelforum.com/showthread...hreadid=508160 |
|
#18
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
sum of 1 column if info in another = specific text
In article ,
blackstar wrote: now that that's fixed.. is it possible using that formula to have it readjust itself if rows are added or removed. ex a2:a14 exists but if i add 5 people i want to formula to include a2:a21. same if i were to remove 2 people so i would only want it to look @ cell a2:12. i know this is easy to do manually but i need this setup so the dumbest person can use it easily. Since you have only one condition, you can use SUMIF instead which will allow you to reference a whole column... =SUMIF(A:A,"Spring",B:B) Also, SUMIF is more efficient than SUMPRODUCT. Alternatively, you can define a dynamic range... Insert Name Define Name: CondRange Refers to: =Sheet1!$A$2:INDEX(Sheet1!$A$2:$A$65536,MATCH(REPT ("z",255),Sheet1!$A$2:$ A$65536)) Click Add Name: RangeToSum Refers to: =Sheet1!$B$2:INDEX(Sheet1!$B$2:$B$65536,MATCH(REPT ("z",255),Sheet1!$A$2:$ A$65536)) Click Ok Change the 'Names' and sheet references accordingly. Then, use the following formula... =SUMIF(CondRange,"Spring",RangeToSum) Hope this helps |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Need to Improve Code Copying/Pasting Between Workbooks | Excel Discussion (Misc queries) | |||
| Positioning Numeric Values Resulting from 6 Column Array Formula | Excel Worksheet Functions | |||
| match and count words | Excel Worksheet Functions | |||
| Count Position of Filtered TEXT cells in a column | Excel Worksheet Functions | |||
| another text to column problem | Excel Worksheet Functions | |||
Linear Mode
