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

Try...

=SUMIF(A1:A100,"<>Spring",B1:B100)

Hope this helps!

In article <[email protected]>,
blackstar <[email protected]> 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

=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

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

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 <[email protected]>,
> blackstar <[email protected]> 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
>

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

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 <[email protected]>,
blackstar <[email protected]> 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 <[email protected]>,
> > blackstar <[email protected]> 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
> >

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 <[email protected]>,
> > blackstar <[email protected]> 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
> >

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 <[email protected]>,
> > > blackstar <[email protected]> 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
> > >

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 <[email protected]>,
> blackstar <[email protected]> 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 <[email protected]>,
> > > blackstar <[email protected]> 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
> > >
>

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))

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

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 <[email protected]>,
> > > > blackstar <[email protected]> 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
> > > >

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
>
>

In that case, try...

=SUMIF(A1:A100,"Spring",B1:B100)

Hope this helps!

In article <[email protected]>,
blackstar <[email protected]> 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 <[email protected]>,
> > blackstar <[email protected]> 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 <[email protected]>,
> > > > blackstar <[email protected]> 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
> > > >
> >

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
>
>

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
> >
> >

In article <[email protected]>,
blackstar <[email protected]> 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