Total cells of one column based on the values in another column?

[Riccol]

One of the sheets in my ledger workbook is for annual cash receipts.
Column D is for the amount (formatted to number-accounting)and Column E
is for who it's from (formatted to number-text). I've got my cells
elsewhere for month, quarter, & year totals.

The problem is I have to manually, with my adding machine, go thru and
add up the total from each source. (How much total income for the year
is/was from Co.A, Co.B, Co.C, etc..)

I've figured out how to use conditional formatting so that if the text
in Column E cells is say, Co.A, the text will be red, if Co.B, text will
be blue, etc ... Good for visually identifying but I still have to
manually add totals for each company.

Is it possible to make a formula that would go something like "look at
cells E5 thru E100. If a cell contains 'Co.A', then add the value of its
adjacent cell (D - where the number amount actually is) to the total". ??

Sounds far fetched to me, but I figured it's worth asking since I know
Excel can do some really complicated formulas if you know how to create
them.

RC (Excel 2002)

 

[T. Valko]

Try this:

=SUMIF(E5:E100,"Co.A",D5100)

Better to use a cell to hold the criteria:

A1 = Co.A

=SUMIF(E5:E100,A1,D5100)

 

[Stu]

You can set up totals for each company by using the formula
=SUMIF($E$5:$E$100,"Co.A",$D$5:$D$100) and changing the value of CoA as
relevant. The only trouble with this is that you have to be consistent and
careful about the spelling of the company names. As a double check, you
could set up totals for E5 thru E100 and for the derived sums for the
individual companies, and set conditional formatting to colour the cell red
if these do not match???

Hope this sets you on your way

Stu

 

[Riccol]

I put the text criteria in its own cells as suggested and used the SUMIF
formula and there it is - totals by company, done automatically. This is
great! And with it automatically doing the totals, I don't even need the
visual cue of different text colors.

I've only got 4 Co's I need to sort and I use 3-letter abbreviations for
them, so I don't think I need the double-check, but just for the
learning aspect, I'm going to try and apply that suggestion next.

RC

 

[Riccol]

I'm trying to put a double-check in place and as easy as it seems it
ought to be, I can't get it to work right.

D164 - formula is =SUM(D160163)

G162 - formula is =G40+G80+G120+G160
(This cell is a merged cell, so it's actually cells G162 and G163)

To double check that the values of D164 & G162 match, I'm trying to add
conditional formatting to D164.

I've tried:

Cell Value IS/not equal to/G162

and

Cell Value IS/not equal to/G163

and

Cell Value IS/not equal to/G40+G80+G120+G160

The format should be red and strike-thru if values don't match.

The values DO match, but for each of the above conditions I tried, the
number in D164 is shown in red and strikethru.

What am I doing wrong? (Besides using merged cells, which I see from
reading here is frowned upon.)

 

[T. Valko]

What kind of numbers are in these cells? If they're decimal values the
*displayed* value may not be the *true underlying* value. For example:

G162 true underlying value = 10.999999999999999

But it will display as 11. If D164 true underlying value = 11 then those 2
values do not match.

To fix that use rounding:

I always use the Formula Is option in conditional formatting...

=ROUND(G162,2)<>ROUND(D164,2)

 

[Riccol]

Thanks, Biff. But they're just regular numbers formatted in "accounting"
style to show two digits after the decimal ($ 0.00). I only ever enter
whole exact dollars & cents ($ 00.00) and I'm not using any formulas
anywhere that would result in more than two digits after the decimal
(like dividing might, for example) so there's no where that the actual
value would be differnt from the displayed value.

 

[T. Valko]

Yeah, but you're using a SUM formula so the values are *calculated*.

Did you try what I suggested?

 

[Riccol]

I hadn't tried it because it didn't understand how the displayed values
could possibly be different than the actual values. But I tried it just
now, and you are correct, using "Formula IS" and your formula works
perfectly. I still don't understand it, but it IS working as it should
so I appreciate you "forcing" me to try it.

 

[T. Valko]

Good deal!

I won't steer you wrong! <g>

 

[Stu]

Use Formula IS D164/not equal to/G162

I'm trying to put a double-check in place and as easy as it seems it
ought to be, I can't get it to work right.

D164 - formula is =SUM(D160163)

G162 - formula is =G40+G80+G120+G160
(This cell is a merged cell, so it's actually cells G162 and G163)

To double check that the values of D164 & G162 match, I'm trying to add
conditional formatting to D164.

I've tried:

Cell Value IS/not equal to/G162

and

Cell Value IS/not equal to/G163

and

Cell Value IS/not equal to/G40+G80+G120+G160

The format should be red and strike-thru if values don't match.

The values DO match, but for each of the above conditions I tried, the
number in D164 is shown in red and strikethru.

What am I doing wrong? (Besides using merged cells, which I see from
reading here is frowned upon.)