I give up! Nested IF, THEN operations plus COUNT to return a single sum??

[lcb803]

I am a relative Excel novice, but have thoroughly explored MS Excel Help and other online help forums for an answer to my question, and at this point, I am wondering if what I want to do is even possible? Please help!

I want the formula to return a single sum for multiple conditions (two, to be exact) in a range to a single cell.

The context of my question is a calendar where IF a day (i.e., a cell) has the value "I" in it, THEN I want the formula to COUNT that value as 0.5 (i.e., half of a day).

IF a day (i.e. a cell) has the value "II" in it, THEN I want the formula to COUNT that value as 1 (i.e., a whole day). THEN I want the formula to calculate the sum of the values within the range specified (i.e., the month).

The answer should be 11.5.

[TABLE="class: outer_border, width: 500, align: left"]
<tbody>[TR]
[TD]SUN[/TD]
[TD]MON[/TD]
[TD]TUE[/TD]
[TD]WED[/TD]
[TD]THU[/TD]
[TD]FRI[/TD]
[TD]SAT[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD]2[/TD]
[TD]3[/TD]
[TD]4[/TD]
[TD]5[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD="align: center"]II[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]7[/TD]
[TD]8[/TD]
[TD]9[/TD]
[TD]10[/TD]
[TD]11[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD="align: center"]II[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]
[/TD]
[TD="align: center"]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]14[/TD]
[TD]15[/TD]
[TD]16[/TD]
[TD]17[/TD]
[TD]18[/TD]
[TD]19[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]21[/TD]
[TD]22[/TD]
[TD]23[/TD]
[TD]24[/TD]
[TD]25[/TD]
[TD]26[/TD]
[/TR]
[TR]
[TD]
[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"]II[/TD]
[TD="align: center"]II[/TD]
[/TR]
[TR]
[TD]27[/TD]
[TD]28[/TD]
[TD]28[/TD]
[TD]30[/TD]
[TD]31[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
[TR]
[TD="align: center"]II[/TD]
[TD="align: center"]I[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[TD]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Tom Urtis]

Maybe this?

=COUNTIF(A2:G11,"II")+(COUNTIF(A2:G11,"I")*0.5)

 

[Momentman]

You could also use the array formula

=SUM(IF($A$1:$G$11="I",0.5,IF($A$1:$G$11="II",1,0)))

It must be entered with ctrl+shift+enter

 

[T. Valko]

I am a relative Excel novice, but have thoroughly explored MS Excel Help and other online help forums for an answer to my question, and at this point, I am wondering if what I want to do is even possible? Please help!

I want the formula to return a single sum for multiple conditions (two, to be exact) in a range to a single cell.

The context of my question is a calendar where IF a day (i.e., a cell) has the value "I" in it, THEN I want the formula to COUNT that value as 0.5 (i.e., half of a day).

IF a day (i.e. a cell) has the value "II" in it, THEN I want the formula to COUNT that value as 1 (i.e., a whole day). THEN I want the formula to calculate the sum of the values within the range specified (i.e., the month).

The answer should be 11.5.

[TABLE="class: outer_border, width: 500, align: left"]
<TBODY>[TR]
[TD]SUN
[/TD]
[TD]MON
[/TD]
[TD]TUE
[/TD]
[TD]WED
[/TD]
[TD]THU
[/TD]
[TD]FRI
[/TD]
[TD]SAT
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD]1
[/TD]
[TD]2
[/TD]
[TD]3
[/TD]
[TD]4
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]II
[/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]7
[/TD]
[TD]8
[/TD]
[TD]9
[/TD]
[TD]10
[/TD]
[TD]11
[/TD]
[TD]12
[/TD]
[/TR]
[TR]
[TD]II
[/TD]
[TD]II
[/TD]
[TD]II
[/TD]
[TD]I
[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD]13
[/TD]
[TD]14
[/TD]
[TD]15
[/TD]
[TD]16
[/TD]
[TD]17
[/TD]
[TD]18
[/TD]
[TD]19
[/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD]20
[/TD]
[TD]21
[/TD]
[TD]22
[/TD]
[TD]23
[/TD]
[TD]24
[/TD]
[TD]25
[/TD]
[TD]26
[/TD]
[/TR]
[TR]
[TD]

[/TD]
[TD]I
[/TD]
[TD]II
[/TD]
[TD]II
[/TD]
[TD]II
[/TD]
[TD]II
[/TD]
[TD]II
[/TD]
[/TR]
[TR]
[TD]27
[/TD]
[TD]28
[/TD]
[TD]28
[/TD]
[TD]30
[/TD]
[TD]31
[/TD]
[TD]

[/TD]
[TD]

[/TD]
[/TR]
[TR]
[TD]II
[/TD]
[TD]I
[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[TD]

[/TD]
[/TR]
</TBODY>[/TABLE]

One way...

=SUM(COUNTIF(A1:C5,{"I","II"})*{0.5,1})

Adjust the range to suit.

 

[lcb803]

T.Valko and Momentman-- many thanks! Both worked. Thank you so much to everyone who responded. If only I had asked this question before spending countless hours Lesson learned!

 

[T. Valko]

T.Valko and Momentman-- many thanks! Both worked. Thank you so much to everyone who responded. If only I had asked this question before spending countless hours Lesson learned!

You're welcome. Thanks for the feedback!

 

[Momentman]

You are welcome

 

[rmtaylor]

Just to say after a general search on the board for guidance form previous questions. Thanks to Tom Urtis the solution you provided (below), works for me , much appreciated. Robert
=COUNTIF(A2:G11,"II")+(COUNTIF(A2:G11,"I")*0.5)

 

[Tom Urtis]

Very nice of you to post that thanks and recognition, Robert...much appreciated.