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

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

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

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

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

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

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