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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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