Expected output based on 3 dropdowns

**Neilesh Kumar** · 05-16-2021, 10:22 AM

Dear Experts,

Please find here attached workbook, where i do expect the output in from Cell C10 to Column G in Status Tab. I have created 3 filters to get expected output. The first filter is for tab name, the second one is for Ownership and the third one is for Status.

The most important part is if we select "All" from the first filter for Tab Selection then the expected output will cover from both the tabs or else will be based on tab selection names.

I hope i have clarified well my expectations. Request you to please do help me on the same and please do revert back in case of any further clarification, if required.

Thank you for your valuable support and precious phase.

Regards,

Neilesh

**JohnTopley** · 05-16-2021, 11:06 AM

First recommendation is you combine data into one tab as extracting from multiple sheets is complex.

And why use combo boxes instead of Data Validation?

And can we have "ALL" ownership but selected "Status" e.g "ALL" & "RNR"

**Neilesh Kumar** · 05-16-2021, 11:37 AM

Hi John,

Thank you for recommendation, but the problem is combining data into one tab is not possible because both tabs data are different and in real time both headers are also bit different. Yes data validation can be used if the expected outcomes remains same with Data Validation.

Request you please do help me. Thank you once again for valuable support.

Regards,

Neilesh

**JohnTopley** · 05-16-2021, 11:47 AM

In your sample file both tabs are the same with regard to "headers" and content. If this is not the real situation please post a fil that is representative of your real situation.

And how different many tabs are there?

And can we have "ALL" ownership but selected "Status" e.g "ALL" & "RNR" ?

**Neilesh Kumar** · 05-16-2021, 11:50 AM

Yes Definitely John, We can have "ALL" Ownership but selected "Status" either for ALL or RNR based on dropdown selection.

The main criteria is the first one based on the first criteria selection rest other two will give the output and based on first and second criteria selection third dropdown will provide the output.

Regards,

Neilesh

**Neilesh Kumar** · 05-16-2021, 12:04 PM

Hi John,

I have verified both the tabs and both the tabs headers are same and accordingly the data can be merged below the data from first tab. Request to you please do help me if it is not possible without merging two different tabs data then please proceed with data merge in a tab.

Regards,

Neilesh

**JohnTopley** · 05-16-2021, 12:13 PM

I am not sure I can provide a formula solution as selecting based on "ALL" - both for tabs (if not merged) and criteria is complicated.

**kvsrinivasamurthy** · 05-16-2021, 12:18 PM

With some guess formulas are given in the file. Pl check file.
Formulas are dragged down.

**Neilesh Kumar** · 05-16-2021, 12:32 PM

Hi John,

Both tabs headers are same so the data can be merged for both the tabs.

Regards,

Neilesh

**JohnTopley** · 05-16-2021, 12:38 PM

I mis-understood what was required!!! "Simple" counts apparently!

**Neilesh Kumar** · 05-16-2021, 12:38 PM

Hi kvsrinivasamurthy,

Thank you for valuable support but when i am trying to filter as "ALL" with first tab then it is giving error. As mentioned both the tabs data can be merged. So would like to request you if possible with merging the data from rest other two tabs and get the expected output.

Regards,

Neilesh

**Neilesh Kumar** · 05-16-2021, 12:41 PM

Dear Experts,

Please find here attached upgraded workbook where I have merged the data. Is it possible now to get the expected output based on three filters. The count for Status will be as "Complete" and Approved as "Yes".

Thank you for your valuable support and precious phase. Request you to please do revert back in case of any further clarification, if required.

Regards,

Neilesh

**JohnTopley** · 05-16-2021, 01:47 PM

Confused: you can select status but Status count is ONLY on "Completed" ?

**Neilesh Kumar** · 05-16-2021, 02:05 PM

Yes John, The status count will be based on Selection from third dropdown. Like if we select as "Complete" then the count of Complete and if select RNR then count of RNR for Ownership and reconciler.

I hope i have clarified expected outcome. Please do revert back for further clarification, if required.

Thank you for valuable support.

Regards,

Neilesh

**JohnTopley** · 05-16-2021, 02:42 PM

See the attached which does NOT deal with a complexity of the "ALL" situations.

in C1

=IFERROR(INDEX(AU!$D$2:$D$7,AGGREGATE(15,6,1/(AU!$D$2:$D$7=$C$4)/(AU!$G$2:$G$7=Status!$C$6)*ROW($A$1:$A$6),ROWS($1:1))),"")

in D10

=IFERROR(INDEX(AU!$F$2:$F$7,AGGREGATE(15,6,1/(AU!$D$2:$D$7=Status!$C$4)/(AU!$G$2:$G$7=Status!$C$6)*ROW($A$1:$A$6),ROWS($1:1))),"")

in E10

=IF( $C10="","",COUNTIFS(AU!$D$2:$D$20,$C$4,AU!$F$2:$F$20,$D10,AU!$G$2:$G$20,$C$6))

in F10

=IF( $C10="","",COUNTIFS(AU!$D$2:$D$20,$C$4,AU!$F$2:$F$20,$D10,AU!$G$2:$G$20,$C$6,AU!I2:I20,"Yes"))

**Bo_Ry** · 05-16-2021, 02:49 PM

Please try at
C10:Dxx

Formula:

Please Login or Register  to view this content.

E10
=IF(C10="","",COUNTIFS(AU!$G$2:$G$99,IF($S$2="All","*",$S$2),AU!$D$2:$D$99,$C10,AU!$F$2:$F$99,$D10))

F10
=IF(C10="","",COUNTIFS(AU!$I$2:$I$99,"Yes",AU!$D$2:$D$99,$C10,AU!$F$2:$F$99,$D10))

**Neilesh Kumar** · 05-17-2021, 12:18 AM

Thank you so much Dear Experts for your valuable support.

Regards,

Neilesh

**Neilesh Kumar** · 05-17-2021, 04:22 AM

Dear Experts,

Thank you for your valuable support and precious phase.

Regards,

Neilesh

Expected output based on 3 dropdowns

LinkBack

Thread Tools

Rate This Thread

Display

Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Re: Expected output based on 3 dropdowns

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] issue with actual formula packsize drink split giving un expected output for specific data

Formula output not as expected.

[SOLVED] VBA for dropdowns based on another dropdowns

[SOLVED] Choose Value From Table Based on 3 Dropdowns

Loop formula to search through range to get expected output

[SOLVED] look up based on multiple dropdowns

Excel 2007 : Excel 2007 Reverse the Expected Output

Bookmarks

Bookmarks

Posting Permissions