# 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

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

#### 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
1
Views
607
Replies
0
Views
131
Replies
7
Views
304
Replies
1
Views
545
Replies
23
Views
4K

### Forum statistics

1,175,973
Messages
5,900,646
Members
434,840
Latest member
ediaz13 ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?    Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back