IF Formula with Drop Down Lists help

[ny74321]

Hello!

I am currently running into issues while using the If Formula with multiple drop down lists. Currently, there is a mandatory drop down on sheet 1 in A8 with five options. There is also a mandatory drop down list on sheet 1 in B8 that is reliant on A8 selection. There is then three more alternative drop down lists on sheet 1 (C8, C9, C10) in which a user of the worksheet CAN choose a selection but doesn't necessarily have to. My current formula for one equation (E8) which is reliant on other sheet's information is:


=IF(Or(C8="",C9="",C1=""),SUMIF('SHEET2'!E3:E900,'SHEET1!B8,'SHEET2'!J3:J900),SUMIFS('SHEET2'!J3:J900,'SHEEET2!'!E3:E900,'SHEET1'!B8,'SHEET2'!H3:H900,'SHEET1'!C8,'SHEET2'!B3:B900,'SHEET1'!C9,'SHEET2'!I3:I900,'SHEET1'!C10))

This formula currently works in that the drop down for A8 and B8 yield the correct sum, but C8, C9, and C10, cant work independently. For example, if you choose the optional dropdown from C8, you have to choose one from C9 and C10 for the sum to be accurate. Alternately, if you erase the drop down from C10, it reverts to the answer for just A8 and B8 even those there are still selections in C9 and C10. Is there a way for these to be independent? If not, is there a Macro I can use?

Answering would be a HUGE help, please and thank you!!!!!!!

 

[DKaur]

Not sure if I understand correctly but I'll give it a try.

Firstly, I think you have some errors, not sure if they are just typed or if the error is in the cell. Please ignore if it is actually correct but here is what I think you should have, just tidying it up a little, not changing the big structure:
=IF(Or(C8="",C9="",C10=""),SUMIF('SHEET2'!E3:E900,'SHEET1'!B8,'SHEET2'!J3:J900),SUMIFS('SHEET2'!J3:J900,'SHEET2'!E3:E900,'SHEET1'!B8,'SHEET2'!H3:H900,'SHEET1'!C8,'SHEET2'!B3:B900,'SHEET1'!C9,'SHEET2'!I3:I900,'SHEET1'!C10))

Secondly your OR statement works such that if any or all of the drop downs in C8, C9, or C10 is entered, then your first sumif will be implemented:
SUMIF('SHEET2'!E3:E900,'SHEET1'!B8,'SHEET2'!J3:J900)
Which is unaffected by your C8, C9 and C10 entries.

Only if all drop downs in C8, C9 or C10 are blank then your second sumif is implemented:
SUMIFS('SHEET2'!J3:J900,'SHEET2'!E3:E900,'SHEET1'!B8,'SHEET2'!H3:H900,'SHEET1'!C8,'SHEET2'!B3:B900,'SHEET1'!C9,'SHEET2'!I3:I900,'SHEET1'!C10)
Which will only sum the cells in row J where the corresponding cells in cols I,B and H are blank.

I think your if statements are the wrong way around. Maybe that will fix it.

 

[ny74321]

Thank you! Is there a formula you would suggest so that my If statements would be correct?

 

[DKaur]

Your formula should be fine just switch the true false sumifs around like:

=IF(Or(C8="",C9="",C10=""),SUMIFS('SHEET2'!J3:J900,'SHEET2'!E3:E900,'SHEET1'!B8,'SHEET2'!H3: H900,'SHEET1'!C8,'SHEET2'!B3:B900,'SHEET1'!C9,'SHEET2'!I3:I900,'SHEET1'!C10),SUMIF('SHEET2'!E3:E900,'SHEET1'!B8,'SHEET2'!J3:J900))

 

[ny74321]

Unfortunately this still groups the three drop downs (c8,c9,c10) so that all need to be filled out or blank. Is there any way to find the sum if just one is chosen? Or two?

 

[ny74321]

Would anyone be able to supply the correct macro for my sumif and sumifs statements and maybe I could try to set the ranges as the c8,c9,c10 drop downs? Sorry I'm just so frustrated over this!

 

[DKaur]

Not sure about the macro, but include a blank cell in each one of your dropdowns (probably just at the end). Then when you want to keep any blank, choose this from the dropdown menu instead of just clearing the cell.