Sumif formula excluding and including a category value based on the dropdown selection

[Neilesh Kumar]

Dear Experts,

Please find here sample workbook, The output formula in Report tab Cell C4:C6 is covering the Service Hours as well from Monthwise Summary tab which is non monetary part.

Dear Experts, I do expect when i select the All option from the dropdown then only the monetary value should be added and displayed with Currency format for the particular Account Manager excluding the Service Hours but when i do select the Service Hours option from the dropdown then i do expect only the Service Hours numbers which is i am getting from the current formula but that i do expect in the number format.

Rest all other dropdowns options, i do expect in the currency format as well except Service Hours.

Dear Experts, i have mentioned my expected results in the sample workbook. Request to you please do provide your precious expertise and valuable support.

Many thanks and kind regards,

Neilesh

[AliGW]

Why should the return value NOT include service hours when ALL is selected? That is counter-intuitive.

There are much easier ways of setting this up.

[63falcondude]

In C4 of the 'Report' worksheet, try this:

=IF(Working!$E$1="ALL",SUMIFS('Monthwise Summary'!G$4:G$24,'Monthwise Summary'!B$4:B$24,B4,'Monthwise Summary'!C$4:C$24,"<>Service Hours"),SUMIFS('Monthwise Summary'!G$4:G$24,'Monthwise Summary'!B$4:B$24,B4,'Monthwise Summary'!C$4:C$24,"Service Hours"))
Format C4:C7 as Currency.

You can then apply Conditional Formatting to make C4:C7 show as a number when the drop down shows "Service Hours" like this:

Select C4:C7 > Conditional Formatting > New Rule > Use a formula
=Working!E$1="Service Hours"
Format: Number > Number > 0 Decimal Places > OK > OK

[63falcondude]

I just realized that there will likely be more that you are looking for than what you included in your expected results. The following will work for all of the dropdown options.

Try this in C4:

=IF(Working!$E$1="ALL",SUMIFS('Monthwise Summary'!G$4:G$24,'Monthwise Summary'!B$4:B$24,B4,'Monthwise Summary'!C$4:C$24,"<>Service Hours"),INDEX('Monthwise Summary'!G$4:G$24,INDEX(MATCH(B4&Working!E$1,'Monthwise Summary'!B$4:B$24&'Monthwise Summary'!C$4:C$24,0),0)))

[Neilesh Kumar]

Thank you so much Dear Expert 63falcondude for your precious phase and valuable support. Your provided solution worked perfectly. Thank you so much once again.

Regards,

Neilesh

[63falcondude]

You're welcome. Thanks for the rep!