How to use text value in cell as criteria [Solved/Closed]

Report
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
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

Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
you can use the cell address instead of typing in the value

=SUMIF(A:A; "=" & C1; B:B)
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2929 users have said thank you to us this month

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...
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
760
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)