I'm trying to use a drop down list with an if statement so when I choose a company from a drop down list some information will automatically fill in.

The company names are in column AF, the year is in column E, the claims info is in AB; SL Prem in AC; CF in AD; and Exp in AE. I need the claims, SL Prem, CF and EXP to automatically change when I select a company from the drop down list.

How can I write a formula so that when I select "company 1" it automatically sums all the claims information for 2013, 2014, etc.

A screenshot of how I have it organized:

Inputs & Outputs

  • 1
    Look into SUMIFS(), but that depends on how your data is set up. You have shown us the output but not the input, so it is hard to give you anything more specific. – Scott Craner Apr 23 '18 at 22:48
  • Hmmm I did an IF statement with a SUMIFS and it worked for the first group, but when I tried to add a second IF statement so that when aa19="company 2" adds up all of their info it was giving me a #VALUE. I'll attach a picture of the inputs as well :) – aicylaa Apr 23 '18 at 23:04
  • Please edit your question and post the formula you have tried. I don't understand your screenshot. Everything is zero or empty. What is the input? what is the expected output? – teylyn Apr 24 '18 at 0:48

Do you want to get the Table like this? enter image description here

enter image description here

If yes, please use this formula:


enter image description here


I would like to suggest a two step solution to fix the issue:

enter image description here

Step 1:

  • Enter this array (CSE) formula in Cell I65, finish with Ctrl+Shift+Enter & fill down.

    {=IFERROR(INDEX($F$65:$F$73, MATCH(0, COUNTIF($I$64:I64,$F$65:$F$73), 0)),"")}
  • From Data TAB click Validation then from Allow, select List then assign range I65:I67 to Source.

Step 2:

  • Write this array (CSE) formula in cell B78, finish with Ctrl+Shift+Enter & fill down.

    {=IFERROR(INDEX($A$65:$A$73, MATCH(0, IF($A$75=$F$65:$F$73, COUNTIF($H$77:$H77, $A$65:$A$73), ""), 0)),"")}
  • Enter this formula in cell C78 & fill across.

    =IF(SUMIFS(B$65:B$73,$F$65:$F$73, $A$75,$A$65:$A$73,$B78)=0,"",SUMIFS(B$65:B$73,$F$65:$F$73, $A$75,$A$65:$A$73,$B78))


  • Keep selecting Underwriter/Company name from Drop Down in cell A75 to get claim details .

  • For neatness later on you may hide range I64:I67.

Adjust cell references in the formula as needed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.