Im making a spreadsheet with different books people have to read and when the person gets finished reading the books they will mark and x and the box. So i have a formula for the following book columns E3,G3,I3and K3 which is COUNTIF(B3:L3,"X")*20. Can i add this formual to another so that N3 and O3 are worth 2.5?
+1
A:
You can cascade them:
=IF( A1="x", 10, IF( A2="X", 20, 0 )
Have a look at COUNTIF though, if every X adds the same value:
=COUNTIF( A1:A20, "X" )
Peter Lang
2010-02-04 08:00:41
ok so it will add the 4 boxes together to equal 60. cause there are 4 boxes that equal 20
Megan
2010-02-04 08:02:46
COUNTIFS(E3,G3,I3,K3="x","20","0") i typed this in and it gave me an error
Megan
2010-02-04 08:06:46
I want to be like if e3 has an x in it then adds 20 to the total if g3 has an x in it to add another 20 to the total and if I3 has an x in it to adds another 20 to the total and if k3 has an x in it to adds another 20 to the total. so once the person is done it will be 60% of the volumns done
Megan
2010-02-04 08:08:58
Sorry, `COUNTIF` only supports ranges, not single cells. The other answers provided seem to be better for your problem. BTW: Why does `20+20+20+20` result in `80`?
Peter Lang
2010-02-04 08:12:14
sorry i meant to say 80. so how do i put a hidden value to cells so they are calculated at the total column when x is entered
Megan
2010-02-04 08:15:34
@Megan: What happened to `F3`, `H3` and `J3`?
KennyTM
2010-02-04 08:26:04
those dont have a value on the cells i wrote above
Megan
2010-02-04 08:27:11
correction- those dont have a value only the cells i wrote above have a value
Megan
2010-02-04 08:28:00
@Megan: So as long as `F3`, `H3` and `J3` do not contain "X", `COUNTIF` will not count them. You can safely use a range (e.g. `=COUNTIF(B3:L3, "X")*20`)
KennyTM
2010-02-04 08:30:29
Ok and if M3 and N3 equal 2.5 how do i add them to the total in the equation
Megan
2010-02-04 08:33:01
@Megan: Maybe you should update your question to display all requirements you want.
KennyTM
2010-02-04 08:36:55
ok is it possible to add different values into one equation?
Megan
2010-02-04 08:38:07
@Megan: Yes, use multiple `SUM`. Edit your question first.
KennyTM
2010-02-04 08:40:26
ok i did. so how do i add the two together
Megan
2010-02-04 08:52:38
+2
A:
You can work with a nested IF, but this may soon become complicated.
In this case, I usually work with a hidden column containing the 'weight' of the column to be marked: say that column A contains the marker for volume A, then column B would contain 20%, and C would contain =IF( A <> "", B, 0 );
Then the sum column would contain =SUM(C,F,I);.
(example: some google doc I made)
xtofl
2010-02-04 08:05:32
+1: The way to go, if you can't use a range or have different weights.
Peter Lang
2010-02-04 08:16:20
+1
A:
Use SUMIF.
(result should be 76%)
Use COUNTIF if the value is fixed.
KennyTM
2010-02-04 08:16:56
I want to be like if e3 has an x in it then adds 20 to the total if g3 has an x in it to add another 20 to the total and if I3 has an x in it to adds another 20 to the total and if k3 has an x in it to adds another 20 to the total. so once the person is done it will be 80% of the volumns done
Megan
2010-02-04 08:18:27
ok you get what i mean. Ok so do i input the formula into each individual box to add a values at the end or do i add the formula into the total box to tell it to put values to the "x"'s and add them
Megan
2010-02-04 08:25:05
@Megan: Just put it in the totals (1 cell). `SUMIF` (and `COUNTIF`) updates automatically as the user put the "X".
KennyTM
2010-02-04 08:28:14
ok so can u give me an example if E3 and G3 are marked with an "x" how it would equal 40
Megan
2010-02-04 08:30:34
i dont work with excel very much. just a one time question thats y im posting on here
Megan
2010-02-04 08:36:30