Multiple IF Formulas???

[nathanmuller]

Hopefully one of you guys can be a legend and help!!! Please find attached.

I can get one part of formula working, but not sure how to add three different formulas together...?!?

Thanks!Book1.xlsx

[FDibbins]

Hi, welcome to the forum

If F2 equals M2, then K4*50%, if not then " "
If G2 equals M2, then K4*25%, if not then " "
If H2 equals M2, then K4*25%, if not then " "

Did you mean M1? Otherwise, M2 is a number, but G2 etc is text?

And where exactly do you want this formula?

I think the basis of what you want is something like...
=K4*IF(F2=??,.5,IF(or(G2=??H2=??),.25,""))

[nathanmuller]

Hi! Sorry;

If F2 equals M1, then K4*50%, if not then " "
If G2 equals M1, then K4*25%, if not then " "
If H2 equals M1, then K4*25%, if not then " "

is it possible to add all of those into M2?

this sheet relates to commission. An invoice is split into 3... (columns F,G,H)

The problem is, is that some times there will be the same initial in columns FGH, but as you can see in row 3, there is a possibility of having three different inititals...

[FDibbins]

Did you try my suggestion?

=K4*IF(F2=M$1,0.5,IF(OR(G2=M$1,H2=M$1),0.25,""))

This assumes that you want F2 to be the priority

(K4 contains 0, so the answer is 0)

[nathanmuller]

Did you try my suggestion?

=K4*IF(F2=M$1,0.5,IF(OR(G2=M$1,H2=M$1),0.25,""))

This assumes that you want F2 to be the priority

(K4 contains 0, so the answer is 0)

Hi. Yes I did and it comes up with £500 (K4 should be K2, my bad)... £500 is correct, however the total in CELL M2 should be £1000 because:

Cell K2 is the total invoice amount
Cells F2, G2, H2 is the invoice split (whoevers initial is in cell F2 gets 50% of the invoice value, initial in G2 gets 25% of invoice value, and initial in H2 gets 25%)

Therefore, invoice value is £1000, as CF is stated in FGH2, CF gets £1000.

I seem to get the £500 okay, but I need the above formula to add all three equations together in M2?

Sorry if I have not explained myself properly!

[Sonia67]

Hi Nathan,

I am new to this forum too, only signed up yesterday.

From what you are explaining, I would say the following formula (Nested IFs) will work.

=IF(F2=M2,K2*50%,IF(G2=M2,K2*25%,IF(H2=M2,K2*25%,"")))

When working with Nested IFs, if you have four true and false values(i.e. in your case 3 if true and 1 if false), you put in the three if true as IFs, the first 2 only have IF True, the the last IF Statement will have the IF false.

Try it, hope it works, meaning that I have understood what you are trying to do.

Regards,

Sonia

[samba_ravi]

M2=SUMPRODUCT(($F2:$H2=M$1)*$K2*{0.5,0.25,0.25})

Please Login or Register  to view this content.

Try this and copy across

[Sonia67]

Hi Nathan,

Please find attached updated spreadsheet that I had not opened earlier.

I have used 3 IF statements and applied conditional formatting to the cells to hide the 0s. I removed the cell colour - if you want it, change the font colour I used for the conditional formatting to the same as the cell colour.

Not sure if the file is attached, let me know if not.

Hope this solves it for you.

Regards,

Sonia

[nathanmuller]

thanks all!! brilliant stuff!!

[nathanmuller]

431375d1448022638-multiple-if-formulas-nathans-spreadsheet.xlsx

Hi Nathan,

Please find attached updated spreadsheet that I had not opened earlier.

I have used 3 IF statements and applied conditional formatting to the cells to hide the 0s. I removed the cell colour - if you want it, change the font colour I used for the conditional formatting to the same as the cell colour.

Not sure if the file is attached, let me know if not.

Hope this solves it for you.

Regards,

Sonia

Thanks Sonia! However, how do I change the cell referencing, so if I had a spread sheet with 100 or more rows? please find attached

[Sonia67]

Hi Nathan,

I meant to ask you if you were going to be data filling it down. I have adjusted the formulas to allow for data filling, you can now data fill them down 1000 rows if you like.

Regards,

Sonia

[nathanmuller]

Sonia, I think I love you Thanks!!!

Now, I need to see if anyone can help me with a Pivot Table using that Spreadsheet!!!

[Sonia67]

Hi Nathan,

You're welcome.

Out of practice a bit with Excel, haven't used it much for 11 years. Just said I'd give it a go, so it was good for me too.

Will keep an eye out, if you put you're spreadsheet example up, I can take a look.

Regards,

Sonia

[nathanmuller]

Book1.xlsx
Hi Sonia

Thanks! Please find attached.

[Sonia67]

Hi Nathan,

I have set up the first one as an example. Is it something like what you are looking for? I think you would need to do one for each employee, as they are in separate columns.

Regards,

Sonia

[nathanmuller]

Hi Sonia

I have added different variables in the clients and candidates...would it be possible to have the candidate and clients listed, like the dates are also?

I should be able to create one for each consultant after that?! Thanks so much

[Sonia67]

Hi Nathan,

Going by what you are saying, if it was me, I would copy the worksheet, a copy for each employee, and hide the columns I don't want. It would take the same length of time, but you will have more control, it would look like the main sheet and you can easily filter it.

Have a look at the attached, CF Table sheet and see what you think.

Regards,

Sonia

[nathanmuller]

perfect! thanks so much Sonia!!

[Sonia67]

Just thought about what I sent you, if your figures change on the main sheet, they will not change on the other sheets, therefore, I have done the first two sheets - CF and SK. When you copy the sheet for each employee, click on the first figure for that emp., and press =, main sheet,same cell, then data fill down. To make sure the figures will update automatically. That is if your figures might change. When you filter CF's figures just select Not Equals To 0.

Good luck with it.

Regards,

Sonia