1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

According to the selection of the drop down list, list only and add

Discussion in 'Ask an Excel Question' started by deciog, Aug 11, 2017.

  1. deciog

    deciog Member

    Messages:
    89
    According to the selection of the drop down list, list only and add


    In this template this summary, original worksheet plus 1000 records

    Thanks in advance for any help.

    Decio of Brazil

    Attached Files:

  2. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,683
    It would help if you let me know what Column I criteria is for. And how it's supposed to filter the data.
    upload_2017-8-11_14-2-29.png

    Edit: Ah, never mind. I found it in the 2nd level header ;)
  3. Chihiro

    Chihiro Well-Known Member

    Messages:
    3,683
    Here you go. I changed column header values for "Entradas" & the other value. Since it wasn't exact match to dropdown value.

    In D22:
    =IFERROR(INDEX($D$5:$D$18,MATCH(0,IF(($C$5:$C$18=$K$4)*(YEAR($B$5:$B$18)=$M$4),COUNTIF($D$21:$D21,$D$5:$D$18),""),0)),"")

    Confirmed as Array (CTRL + SHIFT+ENTER)

    In E22:
    =SUMIFS(INDEX($E$5:$F$18,,MATCH($I$4,$E$4:$F$4,0)),$C$5:$C$18,$K$4,$D$5:$D$18,$D22,$B$5:$B$18,">="&DATE($M$4,1,1),$B$5:$B$18,"<="&DATE($M$4,12,31))

    You can nest E22 formula in =IF(D22="","",Formula), if you don't want to show 0 when Column D is blank.

    Attached Files:

  4. deciog

    deciog Member

    Messages:
    89
    Chihiro, thank you very much.

    It was getting crazy to find the solution, you got it right, fantastic, thank you.

    Decio
  5. bosco_yip

    bosco_yip Well-Known Member

    Messages:
    1,248
    Another option in non-CSE formula solution,

    1] In D22, formula copy down :

    =IFERROR(INDEX(D$5:D$18,MATCH(1,INDEX((C$5:C$18=K$4)*(YEAR(B$5:B$18)=M$4)*(COUNTIF(D$21:D21,D$5:D$18)=0),0),0)),"")

    2] In E22, formula copy down :

    =IFERROR(1/(1/SUMPRODUCT((C$5:C$18=K$4)*(D$5:D$18=D22)*(YEAR(B$5:B$18)=M$4),IF(I$4="Entradas",E$5:E$18,F$5:F$18))),"")

    Regards
    Bosco

    Attached Files:

    deciog likes this.
  6. deciog

    deciog Member

    Messages:
    89
    Bosco

    Very much Thank you, this is perfect, I will use this option is much faster.

    You are execelente in the formulas, fantastic

    Strong hug

    Decio

Share This Page