Calculating text as values in a row
 

I am stumped and wondered if anyone could asisst, I have looked through the
newsgroup but found nothing similar to me problem.

I want to obtain the total number from a text input, in a row (up to 31
cells wide) the following could be entered, FD, AM or PM.
These have different values, FD=1, AM=0.5, PM=0.5.
If one or more of the above are entered in any of the seperate 31 cells I
want a total number.
For example, if FD and AM were entered I would expect a total of 1.5. If
there were 6 FD's and 2 PM's, I would expect a total of 7.

The closest I have got is
=SUMPRODUCT((C11:AG11="FD")--(C11:AG11="AM")--(C11:AG11="PM")), this counts
the entries but I cannot work out how to put the values in.
I have also tried
=SUMPRODUCT(--(C13:L13="FD")*(AN5:AN7="FD")*(AO5:AO7)*SUMPRODUCT (--(C13:L13="AM")*(AN5:AN7="AM")*(AO5:AO7))),
where AN5:AO7 was a table. Still didn't work.

Any assitance would be apreciatted.
Many thyanks
Mick

Calculating text as values in a row
 

One way...

=SUM(COUNTIF(C11:AG11,{"FD","AM","PM"})*{1,0.5,0.5 })

--
Biff
Microsoft Excel MVP


"Mick" wrote in message
...
I am stumped and wondered if anyone could asisst, I have looked through the
newsgroup but found nothing similar to me problem.

I want to obtain the total number from a text input, in a row (up to 31
cells wide) the following could be entered, FD, AM or PM.
These have different values, FD=1, AM=0.5, PM=0.5.
If one or more of the above are entered in any of the seperate 31 cells I
want a total number.
For example, if FD and AM were entered I would expect a total of 1.5. If
there were 6 FD's and 2 PM's, I would expect a total of 7.

The closest I have got is
=SUMPRODUCT((C11:AG11="FD")--(C11:AG11="AM")--(C11:AG11="PM")), this
counts the entries but I cannot work out how to put the values in.
I have also tried
=SUMPRODUCT(--(C13:L13="FD")*(AN5:AN7="FD")*(AO5:AO7)*SUMPRODUCT (--(C13:L13="AM")*(AN5:AN7="AM")*(AO5:AO7))),
where AN5:AO7 was a table. Still didn't work.

Any assitance would be apreciatted.
Many thyanks
Mick

Calculating text as values in a row
 

Biff
Many thanks. works a treat, much apreciatted the swift response.


One way...

=SUM(COUNTIF(C11:AG11,{"FD","AM","PM"})*{1,0.5,0.5 })

--
Biff
Microsoft Excel MVP


"Mick" wrote in message
I am stumped and wondered if anyone could asisst, I have looked through
the newsgroup but found nothing similar to me problem.

I want to obtain the total number from a text input, in a row (up to 31
cells wide) the following could be entered, FD, AM or PM.
These have different values, FD=1, AM=0.5, PM=0.5.
If one or more of the above are entered in any of the seperate 31 cells I
want a total number.
For example, if FD and AM were entered I would expect a total of 1.5. If
there were 6 FD's and 2 PM's, I would expect a total of 7.

The closest I have got is
=SUMPRODUCT((C11:AG11="FD")--(C11:AG11="AM")--(C11:AG11="PM")), this
counts the entries but I cannot work out how to put the values in.
I have also tried
=SUMPRODUCT(--(C13:L13="FD")*(AN5:AN7="FD")*(AO5:AO7)*SUMPRODUCT (--(C13:L13="AM")*(AN5:AN7="AM")*(AO5:AO7))),
where AN5:AO7 was a table. Still didn't work.

Any assitance would be apreciatted.
Many thyanks
Mick

Calculating text as values in a row
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"Mick" wrote in message
...
Biff
Many thanks. works a treat, much apreciatted the swift response.


One way...

=SUM(COUNTIF(C11:AG11,{"FD","AM","PM"})*{1,0.5,0.5 })

--
Biff
Microsoft Excel MVP


"Mick" wrote in message
I am stumped and wondered if anyone could asisst, I have looked through
the newsgroup but found nothing similar to me problem.

I want to obtain the total number from a text input, in a row (up to 31
cells wide) the following could be entered, FD, AM or PM.
These have different values, FD=1, AM=0.5, PM=0.5.
If one or more of the above are entered in any of the seperate 31 cells
I want a total number.
For example, if FD and AM were entered I would expect a total of 1.5. If
there were 6 FD's and 2 PM's, I would expect a total of 7.

The closest I have got is
=SUMPRODUCT((C11:AG11="FD")--(C11:AG11="AM")--(C11:AG11="PM")), this
counts the entries but I cannot work out how to put the values in.
I have also tried
=SUMPRODUCT(--(C13:L13="FD")*(AN5:AN7="FD")*(AO5:AO7)*SUMPRODUCT (--(C13:L13="AM")*(AN5:AN7="AM")*(AO5:AO7))),
where AN5:AO7 was a table. Still didn't work.

Any assitance would be apreciatted.
Many thyanks
Mick