# sum cells with text and formulas

1. ## sum cells with text and formulas

I want to add cells that are using a formula with text
=IF(B3>L3,B3-L3 & " under",L3-B3 &" over")

so the formula gives me a column that looks like
1 over
2 under
0 over
0 over
1 under

I want to add these values up at the bottom to see how many I am off by. The sum formula does not work here though. What can I do? Thanks.  Register To Reply

2. ## Re: sum cells with text and formulas

Do you mean something like:

=COUNTIF(C:C,"under")

or:

=SUMIF(C:C,"under",B:B)

Hope this helps.

Pete

EDIT: Sorry, I didn't realise the numbers and texts were in the same column. How realistic are the values that you quote in your example? i.e. are they always single-digit values?

Pete  Register To Reply

3. ## Re: sum cells with text and formulas

If your data start in B2 for example, then you can use a helper and hidden column to get your results.

So in C2 and copy down.

=LEFT(B2)+0

Then use =SUM(C2:C5)  Register To Reply

4. ## Re: sum cells with text and formulas

Because you are mixing strings with numbers, you cannot add them.

Suggest you change your If Statement to this: =IF(B3>L3,(B3-L3)*-1 ,L3-B3)  Register To Reply

5. ## Re: sum cells with text and formulas

if that totals to 2 under then
=IF(SUM(L3:L10)-SUM(B3:B10)<0,ABS(SUM(L3:L10)-SUM(B3:B10))&" under",SUM(L3:L10)-SUM(B3:B10)&" over")  Register To Reply

6. ## Re: sum cells with text and formulas

Perhaps
=SUMPRODUCT(B3:B7-L3:L7)

or reverse the ranges if a negative result is the Under..  Register To Reply

There are currently 1 users browsing this thread. (0 members and 1 guests) 