exclude value related to drop down list element

[walhir]

hello everyone,
I tried to find the answer for some time now googling and yahooing without any result.
I'm guessing I couldn't figure the proper search term
Anyway my question is:
I'm trying to sum a column of numbers, the numbers are calculated depending on the drop down list choice in adjacent column, i.e. "Fuel", "Work clothes", "Phone bill" and so on. There is one choice called "Tax payments/returns", I'd like to be able to omit its value in summary in case it's been chosen from the drop down list.
I thought about using combination of "sumif" and "if" formulas but after many tries I'm still in limbo .

If anyone could help me with this I'd be truly greatful. Thanks in advance.

 

[FDibbins]

If you have excel 2010 or later, you can use sumifS() function, it allows for more than 1 criteria.

So the formula would look something like this...
=sumifS(sum-range, criteria1-range, criteria1, criteria2-range, criteria2, criteria3-range......................)

=sumifS(D:D,C:C,"FUEL",....
hmm wait. Are you summing by cost category, or total for everything?

Total per category would exclude Tax anyway, no?
Grand total...
=SUMIF(C:C,"<>Tax payments/returns",D:D)
where C=column with categories and D = column with amounts

 

[walhir]

If you have excel 2010 or later, you can use sumifS() function, it allows for more than 1 criteria.

So the formula would look something like this...
=sumifS(sum-range, criteria1-range, criteria1, criteria2-range, criteria2, criteria3-range......................)

=sumifS(D:D,C:C,"FUEL",....
hmm wait. Are you summing by cost category, or total for everything?

Total per category would exclude Tax anyway, no?
Grand total...
=SUMIF(C:C,"<>Tax payments/returns",D:D)
where C=column with categories and D = column with amounts

Thanks for your help,


I was aware of the existence of sumifs formula but I think using it involves entering all the criteria you want to use in sum which could make it sometimes very long formula.
So I though it must be a way to just ask excel to skip (in my case) one criteria and summarize all the rest.
For example I have $20 - fuel, $20 - mobile phone and $10 - tax payment/return. The result of a formula I seek would be $40.


You right about the total by category but to do it this way I need to create another sheet with sum of categories which I didn't need. So far.
I will do it if I won't find the answer for this "exclusion"
I'll try yours " =SUMIF(C:C,"<>Tax payments/returns",D:D)
where C=column with categories and D = column with amounts"


cheers


waldek