Previous Topic Next Topic

How to use text value in cell as criteria

Solved/Closed
thiago - Jul 29, 2010 at 09:21 AM
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 - Jul 30, 2010 at 08:57 AM
hello,

In cell D1, I'm using a SUMIF formula like so

=sum if (A:A; criteria; B:B).

In Column A and B is a list of charges different companies made, the name is on A and the value of the charge is in column B. Each company has multiple charges, so their names will repeat as many times in A.

So I want to sum the total charge for each company seperate.

So what I did is write out their names in Column C, and then what I want to do, starting from the first company in C1 is use the formula i wrote above the display the total sum for that company. but i can't figure out how to use a cell with a text value as a criteria. so for example:

=sum if (A:A; text value in C1; B:B)

I know i can write out the name of the company as the criteria, but if i do it the way i want to i can just drag the formula down the D column next to the C column, which would save time.

2 responses

Answer 1 / 2
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 30, 2010 at 07:34 AM
you can use the cell address instead of typing in the value

=SUMIF(A:A; "=" & C1; B:B)
Answer 2 / 2
thiago
Jul 30, 2010 at 08:45 AM
Thanks that worked! but just to clarify, the cell address has to be without quotes.

so

=SUMIF(A:A; C1; B:B).

before i even posted here i had tried using the cell address, but i put it between quotes which i know realize meant that Excel was looking for the text value C1 as a criteria instead of the cell address. duh...
rizvisa1 Posts 4478 Registration date Thursday January 28, 2010 Status Contributor Last seen May 5, 2022 766
Jul 30, 2010 at 08:57 AM
True. Just a side note (in case you need down the road), by default you are using "=" here. So

=SUMIF(A:A; C1; B:B) is same as =SUMIF(A:A; "=" & C1; B:B)

Lets say you wanted to do a sum if the value were different then you would need
=SUMIF(A:A; "<>" & C1; B:B)

So basically the condition you add as a string before the cell address (which you as noted needs to be outside the quotes)

Similar discussions

Excel - IF function with multiple words
Nick -
user -

14 responses
help me to add particular cells
mathewmunna -
mathew munna -

2 responses
If a cell contains any text then add a value
cbrizz -
Pharmanumbers -

20 responses
How to use conditional format based on text?
Leanne99 -
Mooseman -

26 responses
Fill adjacent cell with number from text answer
MitchP -
vcoolio -

20 responses