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:
 

Excel Facts

Can you AutoAverage in Excel?
There is a drop-down next to the AutoSum symbol. Open the drop-down to choose AVERAGE, COUNT, MAX, or MIN
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")))
 
Upvote 0
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"}
 
Upvote 0
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.
 
Upvote 0
Hi-
if you have these data, what should be the values in columnD?
Book1
ABCD
5CO
6CO
7CO
8CH
9CH
10CH
Sheet1
 
Upvote 0
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.
 
Upvote 0
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:
 
Upvote 0
=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.
 
Upvote 0
=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:
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,998
Members
448,539
Latest member
alex78

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?

Disable AdBlock

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