Two part question: Check my Syntax please?

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
I am certainly a novice. But I've got an elaborate Excel sheet that I've made over a long course of time. I need to expand on it and I need help.

I've got this formula below:

=IF(OR(ISBLANK(B10), ISBLANK(C10)), "", IF(A10="HD", 0.5*(C10-B10+1), C10-B10+1))

What would the syntax be if I wanted to add more items where it reads "HD"?
For example: ..... IF(A10="HD" , "PH" , "OH" , "LH" , "MH" , 0.5*(C10-B10+1, C10-B10+1))

I know my example is incorrect ... I cannot quite get it right ... either commas not in the right place, spaces ... or something. I spent quite awhile trying to find an example ... but unsuccessfully.

The second part of my question:
I have used up the three conditional formats that Excel allows.
I've got it where if "ED" is entered, the cell is formatted a particular color. Same with "LV" and "PD".

For example: .... Formula Is =(A105="LV")
Can I also include other codes here: Such as ... Formula Is =(A105="LV" , "PH" , "MH"?

I'm just not getting the syntax quite correct and would appreciate guidance.

Thanks!
Kevin.


:cool:
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
Hello Kevin,
For example: .... Formula Is =(A105="LV")
Can I also include other codes here: Such as ... Formula Is =(A105="LV" , "PH" , "MH"?
Try something like this:
Code:
=(OR(OR(A105="LV",A105="PH",A105="MH")))
 

barry houdini

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
In both cases you can use OR, e.g.

=IF(OR(ISBLANK(B10), ISBLANK(C10)), "", IF(OR(A10="HD",A10="PH",A10="OD",A10="LH",A10="MH"), 0.5*(C10-B10+1), C10-B10+1))

"formula is"

=OR(A105="LV",A105="PH",A105="MH")

In the worksheet formula you could simplify to

=IF(OR(ISBLANK(B10),ISBLANK(C10)),"",IF(OR(A10={"HD","PH","OD","LH","MH"}),0.5*(C10-B10+1),C10-B10+1))

but conditional formatting doesn't allow array constants like

{"HD","PH","OD","LH","MH"}
 

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
Thanks... I'm a step further

Thanks for the help.

I'm a few steps further.

I have a formula that reads:
=SUM(IF($A$5:$A$93=$A104,$D$5:$D$93))

I was wanting it to do the following ... but something isn't correct.
=SUM(IF($A$5:$A$93={"CO","CH"}),$D$5:$D$93))

Can you help me understand where my mistake is?

Basically, I want my formula to look down column A (from cell 5 to 93) for any entries that equal "CO" or "CH". Wherever it finds either of these two entries, it will take all the totals it finds in column D (from cell 5 to 93) and add them up.

I wish there was some sort of "Excel syntax guide for dummies" out there ... because I dislike having to bother others with my work. I can always get 'just so close' ... but never hit the mark. :(

Thank you for all the help!
Kevin.
 

agihcam

Well-known Member
Joined
Jan 16, 2006
Messages
1,624

ADVERTISEMENT

Hi-
if you have these data, what should be the values in columnD?
Book1
ABCD
5CO
6CO
7CO
8CH
9CH
10CH
Sheet1
 

Special-K

Board Regular
Joined
Apr 18, 2006
Messages
63
Column D contains numeric totals.

COLUMN A = CODE
COLUMN B = START DATE
COLUMN C = END DATE
COLUMN D = TOTAL DAYS

So:

A B C D
CO 10/06/06 10/6/06 1
CH 11/06/06 11/9/06 4
PD 8/02/06 8/6/06 5

Just to recap, the formula would look down column A for any categories "CO" and "CH" ... then get the values from column D ... and total them.

In the above example, the total would be 5 ... (by adding 1+4 and ignoring the row with "PD").

Thanks for the help!
Kevin.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453

ADVERTISEMENT

Hello Kevin,
I'm sure there's a better way (I suck at formulas) but until one comes along this
seems to be working well enough.
Code:
=SUMIF(A5:A93,"CO",D5:D93)+SUMIF(A5:A93,"CH",D5:D93)

Now all we need is someone to show how it really should be done. :rolleyes:
 

PaddyD

MrExcel MVP
Joined
May 1, 2002
Messages
14,234
=SUMPRODUCT(--(ISNUMBER(MATCH(A5:A93,{"CO","CH"},0))),D5:D93)

or

=SUM(IF((A5:A93="CO")+(A5:A93="CH"),D5:D93))

...the latter entered with control + shift + enter, not just enter.
 

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,453
=SUMPRODUCT(--(ISNUMBER(MATCH(A5:A93,{"CO","CH"},0))),D5:D93)
or
=SUM(IF((A5:A93="CO")+(A5:A93="CH"),D5:D93))
Man I wish I could come up with stuff like that. :unsure:
 

Watch MrExcel Video

Forum statistics

Threads
1,113,811
Messages
5,544,437
Members
410,610
Latest member
renatha prado
Top