sum cells with text and formulas

[yankeekid86]

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.

[Pete_UK]

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

[apla]

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)

[alansidman]

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)

[martindwilson]

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")

[Jonmo1]

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


or reverse the ranges if a negative result is the Under..