# Office 365 >  >  How can I get a cell to recognize a value and then add a different cells value to a third?

## notsofast

I have a sheet...see attached............. that I need help with. I color coded it to try and make it easier to understand what I want to do.   So what I want to do is input a dollar amount in the yellow cells.  A letter will be entered into the Blue cells.  This letter will be one of the letters from the purple cells at the bottom.  I need a formula that looks at the letter in the blue cell and then takes the dollar amount from the yellow cell next to it and adds it to a running total in the green cell.  So if the letter in the blue cell is a Z it would get totaled in the green cell below the Z. If the letter in the blue cell is an A it would get totaled to the amount in the green cell below the letter A.  I hope this all makes sense.

----------


## 6StringJazzer

Welcome to the Forum! Every person who asks a question here is stuck and needs help, so your title doesn't tell us much about what you need. Please revise it so that someone reading only your title will have an idea of whether they will know how to help you. Think in terms of someone googling for a problem just like yours and being able to find your title.

_Your post does not comply with Rule 1 of our Forum_ RULES. *Your post title should accurately and concisely describe your problem, not your anticipated solution.*

Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.

*To change a Title go to your first post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.*

*(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)*

----------


## notsofast

changed as requested

----------


## Debraj Roy

Hi NotSoFast!..

Yeah.. NotSoFast.. please spend some time to read the complete request.. Still it was not according to rule..
Read the blue part again.. 

BTW.. welcome to the forum..

*Your post title should accurately and concisely describe your problem*

----------


## notsofast

OK...tried it again.  Not enough room to describe it all.  Hope this one works.

Thanks

----------


## Debraj Roy

thanks for the co-operation...

Check this one..

EF1053251.xlsx



```
Please Login or Register  to view this content.
```



Copy paste the same formula in all GREEN cells..  :Smilie:

----------


## notsofast

That's frickin awesome.

I couldn't get the A and the E to work though.

----------


## Debraj Roy

PINK A & E are actually "A " & "E "

remove those space's..  :Smilie:

----------


## notsofast

I'm sorry I don't follow you.  I need the A and the E.

----------


## Debraj Roy

!!! Do you still need some clarification ???  :Smilie:

----------


## notsofast

Had to read it several times but finally figured it out.

THANKS !!

----------


## Debraj Roy

Oh.. my fault..

I should have tell you that.. those A & E are contain space.. like "A( )" and "E( )"   :Cool: 

thanks for feedback..

----------


## notsofast

It took me a few minutes but I finally got it.  Still frustrated though.  I can not get this to work on the actual spreadsheet.  Can I attach the actual sheet for you to see?

----------


## notsofast

Here is the actual sheet.

----------


## Haseeb A

Try in D40,

=SUMIF($E$15:$S$36,LEFT(D39),$D$15:$R$36)

Then copy to other cells.

----------


## notsofast

That is pretty close except it's not working correctly.   I copied the formula to the first two cells....the X cell..D40..and the Y cell..F40.  When I go back and enter a value in the sheet it all goes to the Y cell even if I put in an X.

----------


## Haseeb A

Works for me. Please see attached.

Note: in SUMIF first range is E:S & 2nd is D;R

----------


## notsofast

WOW...yes the one you sent works.  I cant seem to cut and paste the formula without it getting all messed up.  Will this work with a 2 page sheet as well?

see attached

----------


## Haseeb A

Try this in Sheet2!D38,

=SUMIF(Sheet1!$E$15:$S$36,LEFT(D37),Sheet1!$D$15:$R$36)+SUMIF($E$13:$S$34,LEFT(D37),$D$13:$R$34)

Then copy to other cells.

----------


## notsofast

If I can cut and paste correctly it looks like it will work !!!


Thanks so much !!

----------


## notsofast

Is there a way to add a formula that if I put in a dollar amount but do not enter a letter that the dollar amount will turn red until it is assigned a letter??

----------


## Haseeb A

You can use conditional format,

See attached.

----------


## notsofast

That would be perfect !!  Was the formatting already in the sample?

----------


## Haseeb A

There was some other conditional formatting already in the sample. But the particular one was not.

----------


## notsofast

I cleared all that other stuff out in the actual sheet i'm using....not the sample one here...do I use and IF Than?

----------


## notsofast

I am trying to set this page up so that whatever I type in cell J4 is copied into cell G45 and the data in cell J6 to G47 but I cant seem to get it to work !!  What am I doing wrong?!?!

----------


## Haseeb A

G45 & G47 are 'Text' formatted cells. Right Click from these cells & select 'Format Cells' Select 'General' in Number tab >>OK. Then press F2 & Enter

----------


## notsofast

That was it !!

Thanks

----------

