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.

SUN	MON	TUE	WED	THU	FRI	SAT
		1	2	3	4	5
						II
6	7	8	9	10	11	12
II	II	II	I
13	14	15	16	17	18	19
20	21	22	23	24	25	26
	I	II	II	II	II	II
27	28	28	30	31
II	I

<tbody>
</tbody>

 

[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.

SUN	MON	TUE	WED	THU	FRI	SAT
		1	2	3	4	5
						II
6	7	8	9	10	11	12
II	II	II	I
13	14	15	16	17	18	19
20	21	22	23	24	25	26
	I	II	II	II	II	II
27	28	28	30	31
II	I

<TBODY>
</TBODY>

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.