sum of 1 column if info in another = specific text

[Guest]

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

 

[Domenic]

Try...

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

Hope this helps!

 

[Guest]

=SUMIF(G16:G18,"<>spring",H16:H18)

 

[Guest]

nope, didn't work only gave me the sum of from ex h16:h18 regardless of info
in cells g16:g18

 

[Guest]

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

 

[Guest]

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.

 

[Domenic]

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'.?

 

[Guest]

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

 

[Guest]

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

 

[Guest]

And, A1:A4, ONLY have the value spring typed in. No other text in those cells?

 

[SteveG]

Ok. I know I'm late in the game here but I'll give it a shot. I thin
what you mean is that if the cells in column A contain the word sprin
then include the corresponding value in column B for your sum. I use
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
does not equal spring than,

=SUMPRODUCT(--(A2:A14<>"spring"),(B2:B14)

 

[SteveG]

I think what Jonathan is getting at is that does the text in A1:A
contain additional text like,

The spring...

spring up...

or something like that. If so use wildcard characters before and afte
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

Stev

 

[Guest]

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

 

[Guest]

you are correct. that's what I was getting at.

The sumif should take care of what is being asked.

 

[Domenic]

In that case, try...

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

Hope this helps!

 

[Guest]

we have a winner!!
=sumproduct(--a2:a14="spring"),(b2:b14))
did the job!!
thank you steveg

 

[Guest]

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.

 

[Domenic]

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