Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having a cell show X but read something else?
I have a small spread sheet set up to keep track of a grass cutting contract.
We have our area divided into sections A-G. Each section has a different dollar amount attached to it (section A $500, B $350, C $200, etc.) I list the sections across the top of my spread sheet. Down the side I list the date. If for instance we have the contractor cut sections A, B and C during the first date I put and X in cells B2, C2 and D2. I would like to then have this total $1050. How can I get the cells to read the dollar amount even though I'm typing an X in each cell? Thanks |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having a cell show X but read something else?
Somthing like the below..
Row1 will have the values..A1=500 , B1=350, C1=200 etc; Row2 will have the x markings D2 will have the below formula..Copy down as required for the dates... =IF(A2="x",$A$1,0)+IF(B2="x",$B$1,0)+IF(C2="x",$C$ 1,0) If this post helps click Yes --------------- Jacob Skaria "deertroy" wrote: I have a small spread sheet set up to keep track of a grass cutting contract. We have our area divided into sections A-G. Each section has a different dollar amount attached to it (section A $500, B $350, C $200, etc.) I list the sections across the top of my spread sheet. Down the side I list the date. If for instance we have the contractor cut sections A, B and C during the first date I put and X in cells B2, C2 and D2. I would like to then have this total $1050. How can I get the cells to read the dollar amount even though I'm typing an X in each cell? Thanks |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having a cell show X but read something else?
Say your first set of X's can be entered in B2:D2 with headers, A,B,C in B1:D1 and Dates starting at A2, downwards... So then in E2, enter formula: =SUMPRODUCT(--($B2:$D2="X"),{500,350,200}) copied down... -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109619 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having a cell show X but read something else?
Just now I realised you have more sections. If your x markings are from A to
G with row1 having the values...try the below formula in H2; and copy down as required... =SUMPRODUCT(--(A2:G2="X"),$A$1:$G$1) If this post helps click Yes --------------- Jacob Skaria "Jacob Skaria" wrote: Somthing like the below.. Row1 will have the values..A1=500 , B1=350, C1=200 etc; Row2 will have the x markings D2 will have the below formula..Copy down as required for the dates... =IF(A2="x",$A$1,0)+IF(B2="x",$B$1,0)+IF(C2="x",$C$ 1,0) If this post helps click Yes --------------- Jacob Skaria "deertroy" wrote: I have a small spread sheet set up to keep track of a grass cutting contract. We have our area divided into sections A-G. Each section has a different dollar amount attached to it (section A $500, B $350, C $200, etc.) I list the sections across the top of my spread sheet. Down the side I list the date. If for instance we have the contractor cut sections A, B and C during the first date I put and X in cells B2, C2 and D2. I would like to then have this total $1050. How can I get the cells to read the dollar amount even though I'm typing an X in each cell? Thanks |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having a cell show X but read something else?
Just to add to NBVC's response.
I'd put those amounts in B1:D1 (or maybe a hidden row. Then the formula becomes: =SUMPRODUCT(--($B2:$D2="X"),$B$1:$D$1) Then if your prices change (next year's spreadsheet???), you'll only have to update the new prices in that row. NBVC wrote: Say your first set of X's can be entered in B2:D2 with headers, A,B,C in B1:D1 and Dates starting at A2, downwards... So then in E2, enter formula: =SUMPRODUCT(--($B2:$D2="X"),{500,350,200}) copied down... -- NBVC Where there is a will there are many ways. ------------------------------------------------------------------------ NBVC's Profile: http://www.thecodecage.com/forumz/member.php?userid=74 View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=109619 -- Dave Peterson |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having a cell show X but read something else?
Used NBVC method. Exactly what I was looking for. Thanks to all.
"deertroy" wrote: I have a small spread sheet set up to keep track of a grass cutting contract. We have our area divided into sections A-G. Each section has a different dollar amount attached to it (section A $500, B $350, C $200, etc.) I list the sections across the top of my spread sheet. Down the side I list the date. If for instance we have the contractor cut sections A, B and C during the first date I put and X in cells B2, C2 and D2. I would like to then have this total $1050. How can I get the cells to read the dollar amount even though I'm typing an X in each cell? Thanks |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Having a cell show X but read something else?
I just read Dave's post so I used his modified formula to save me time next
year. Thanks again "deertroy" wrote: I have a small spread sheet set up to keep track of a grass cutting contract. We have our area divided into sections A-G. Each section has a different dollar amount attached to it (section A $500, B $350, C $200, etc.) I list the sections across the top of my spread sheet. Down the side I list the date. If for instance we have the contractor cut sections A, B and C during the first date I put and X in cells B2, C2 and D2. I would like to then have this total $1050. How can I get the cells to read the dollar amount even though I'm typing an X in each cell? Thanks |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to read the value of a cell | Excel Worksheet Functions | |||
CAN YOU SET GRAPH TO READ, FORMULA IN A CELL AS A BLANK CELL | Excel Discussion (Misc queries) | |||
Formatting to show/not show "DUE" and "RECEIVED" in cell | New Users to Excel | |||
Show cells have been read - linked | Excel Discussion (Misc queries) | |||
how read data downloaded on cd-rw, now show only code? | New Users to Excel |