How to use text value in cell as criteria

Solved/Closed
thiago - Jul 29, 2010 at 09:21 AM
rizvisa1
Posts
4479
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 replies

rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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)
1
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...
0
rizvisa1
Posts
4479
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
769
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)
0