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

[deciog]

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

 

[Chihiro]

It would help if you let me know what Column I criteria is for. And how it's supposed to filter the data.


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

 

[Chihiro]

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.

 

[deciog]

Chihiro, thank you very much.

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

Decio

 

[bosco_yip]

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

 

[deciog]

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