How to calculated

[pranvinilesh]

Hi,

Please find attached file. In the file, if I select Location=All & Dept=All, then show all location & All Dept. total amount. Please help.

[AliGW]

In O3 copied down:

=IF($K$3="*ALL",SUMIFS($F$3:$F$16,$E$3:$E$16,N3),SUMIFS($F$3:$F$16,$E$3:$E$16,N3,$D$3:$D$16,$K$3))

[StefanD]

Hello Pranvinilesh,

try this:

[AliGW]

StefanD - sorry for off-topic interjection:

Although there is no official rule regarding this behaviour, we request that wherever possible both the question AND the answer be provided in substantive detail here within the thread. An attached workbook is an excellent aid for posing a question and offering a solution, but solely doing that with no in thread explanation makes it difficult for researchers to understand or consider the Q & A of this thread without downloading what may be a pointless doc to them, if they can do that at all. Doing that also hides the content from search engines so others may never benefit from this.

I'm sure you understand, and we look forward to seeing you post your formulas/macros in your posts for the searching benefit of all.

Thanks again for all your hard work here!

[JohnTopley]

Try

=SUMIFS($F:$F,$C:$C,$K$4,$D:$D,$K$3,$E:$E,$N3)

Change "*All" to "*"

[StefanD]

Dear AliGW,

I found that I used somewhat the same formula as you used, Ill try to explain it a bit, Sorry for my misinterpretation of the forum customs, I am quite new to this forum after all.

@Pranvinilesh: The key to this formula is to use SUMIFS, creating conditions in which your worksheet should SUM the data.

This formula consists of two parts (Ill leave out the $-signs for the sake of readability)

Please Login or Register  to view this content.

You see, you'll want to check if your drop-down list contains *ALL. (shown in blue) If so, you need to SUM all numbers (shown in red).
If not, you want it to check for the value in N3, and display all values corresponding with that specific value, by checking both N3 and K3(shown in green).

I hope my explanation gives you insight in how this formula operates.

Regards,

StefanD

[AliGW]

I hope my explanation gives you insight in how this formula operates.

I think you misunderstood the request. When you present a solution, it is fine to provide a workbook, but you should also say what the formulae are that you have used therein within the post itself. You don't need to explain the formulae unless the OP asks you to.

[pranvinilesh]

Thanks to all. In this file I have use drop down option. If I select "*ALL" then answer get. But if i select any other location or dept. instead of "*ALL" then answer get wrong. Please help. And thanks once again for help.

[AliGW]

My formula, outlined in my first post above, works perfectly in your own file (see attached).

[FDibbins]

Try this, copied down...
=SUMIFS($F$3:$F$16,$C$3:$C$16,IF($K$4="*all","*",$K$4),$D$3:$D$16,IF($K$3="*all","*",$K$3),$E$3:$E$16,N3)

[pranvinilesh]

Yes your formula work, If i choose both location & dept = "*ALL", but if I change Dept. value any other like "Sales" instead of "*ALL" then answer not changed.

[AliGW]

Try Ford's offering in post #10.

[pranvinilesh]

Thanks Ford's formula worked. Also thanks AliGW for your help. And AliGW next time also remember your advice for post.

[AliGW]

The advice was for Stefan, not you. Glad you have your solution!

If that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED. Thanks.

[FDibbins]

Happy to help and thanks for the feedback