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

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

HalfAce

MrExcel MVP
Joined
Apr 6, 2003
Messages
9,454
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,454

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,454
=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:
 

Forum statistics

Threads
1,141,011
Messages
5,703,723
Members
421,311
Latest member
tanujath

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
Top