Solved How to write Sumif with Two exceptions...

September 11, 2018 at 13:58:01
Specs: Macintosh
Hi!!
I am somewhat new to Excel and I am having trouble figuring out how to write this formula.

I have 12 monthly sheets with Commodity futures trading totals being fed into a summary sheet (13th sheet).

On each of the 12 monthly sheets:
Cells A12:A55 are Market Descriptions (Names)
Cells C12:C55 are the corresponding values

3 of the market names that will be in the Market Descriptions are Euro FX (also called Eurodollar or Euro), Euro Bund, and Euro Stoxx.

I am trying to write a formula that will Sumif or Sumifs ,if the Market Name is Euro, but not Euro Bund or Euro Stoxx. I have been looking for a way to write these exceptions, but I am at a loss. The rest of the sheet works perfectly.

I want to write it this way, if possible, because others traders will have these spreadsheets and may put the slang names, (i.e. Euro for Euro FX) and not remember to put the proper names in the description.

Thank you for any help!!!!

message edited by jdueitt


See More: How to write Sumif with Two exceptions...

Reply ↓  Report •

#1
September 11, 2018 at 16:01:39
✔ Best Answer
Here is a SUMPRODUCT() solution:

=SUMPRODUCT(--($A$12:$A$55="Euro FX")--($A$12:$A$55="Eurodollar")--($A$12:$A$55="Euro"),$C$12:$C$55)

See how it works for you.

may put the slang names

To make sure they enter only the names you want consider using a drop down list with Data Validation.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#2
September 11, 2018 at 16:13:05
Mike,

Thank you for the solution. I will try it and see how it works.

A question about it...If I use "Euro" and "Euro Bund" and "Euro Stoxx" are also listed on the Market names all in the same month with values, will this formula count those values together, because they all begin with Euro?

Also, I have not heard of a drop down list with Data Validation. That sounds interesting. I will look into that.

Thank you again !!!!!!!

Jerry

message edited by jdueitt


Reply ↓  Report •

#3
September 11, 2018 at 17:12:53
because they all begin with Euro?

No
Only those names listed in the formula will be totaled.
If you need more names simply add them to the end of the formula in the
same manner as the others, like

=SUMPRODUCT(--($A$12:$A$55="Euro FX")--($A$12:$A$55="Eurodollar")--($A$12:$A$55="Euro")--($A$12:$A$55="Euro Stoxx"),$C$12:$C$55)

If your looking to total all names that begin with Euro then this should work:

=SUMPRODUCT(--(ISNUMBER(SEARCH("Euro*",$A$12:$A$55))),$C$12:$C$55)

For Data Validation look here:

https://www.contextures.com/xlDataV...

MIKE

http://www.skeptic.com/

message edited by mmcconaghy


Reply ↓  Report •

Related Solutions

#4
September 11, 2018 at 17:33:56
Mike,

Thank you!!! Your answer worked perfectly. It did just what I needed.

Thank you also for the link to the Data Validation. This would fix a lot of problems ahead of time for me.

Very much appreciated!!

Jerry


Reply ↓  Report •

#5
September 11, 2018 at 17:35:46
I updated my last post, not sure you saw it or I posted it after you.

MIKE

http://www.skeptic.com/


Reply ↓  Report •

#6
September 11, 2018 at 17:44:56
No, I didn't see the update.

I am adding all 12 months together for the 30 individual market names to get a more accurate reading on how each market performs throughout the year. So, separate is good!!!

Jerry


Reply ↓  Report •

Ask Question