# Two part question: Check my Syntax please?

#### Special-K

##### Board Regular
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. ### 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
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
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
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

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

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. ##### MrExcel MVP
=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.

#### Special-K

##### Board Regular
Looks like it works just fine.

Thanks mate!

- Kevin.

#### HalfAce

##### MrExcel MVP
=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. Replies
3
Views
318
Replies
1
Views
302
Replies
14
Views
5K
Replies
0
Views
343
Replies
1
Views
544