Double Conditional in SUMIF

[metsci]

I need the equivalent of a double conditional in the Function SUMIF.

In my example (a test file is attached), I have used hypothetical stock trades as a test case.

Column B has the type of order (Buy or Sell).
Column C has the stock trading symbol.
Column F has the net amount of the transaction on that line.

I want to populate Columns I & J, where I & J are the total Buy and Sell cost for each stock listed in Column H.

So, for example I need to calculate the total "Net Amount" of "Buy" transactions for Stock ABC, and put it in Cell I2. I also need to put the the total "Net Amount" of "Sell" transactions for Stock ABC, and put it in Cell J2.

I'm sure that looking at the attached sample EXCEL workbook, will make it clearer than all my verbiage.

So, conceptually, I need to evaluate Columns B, C and F and put the sum of transactions in Cell I2 for those cases with "Buy" in Column B AND "ABC" in Column C.

If there is no syntax for a double conditional in SUMIF, then I'd be happy with any formula for Cell I2 that accomplished the tax.

I'm not terribly familiar with array formulas, but have used them on occasion if that's what's required to emulate a double conditional in SUMIF.

Thanks in advance for any help you can give,
metsci

[TMS]

In 2003:

Please Login or Register  to view this content.

Regards, TMS

[Richard Buttrey]

Hi,

If you do only have Excel 2003 then you'll have to fall back on the old SUMPRODUCT() function. i.e. in I2 copied down

Please Login or Register  to view this content.

and similarly in J2 changing "BUY" to "Sell"

If you do however have Excel 2007 or later then use SUMIFS()

[metsci]

Richard Buttrey,

Mega-mega thanks!

Your EXCEL 2003 suggestion really does a nice job.

As an aside:
I have EXCEL 2003 and EXCEL 2013, but use EXCEL 2003 about 95% of the time because it's so much easier to use and because I can then give workbooks to friends who only have EXCEL 2003.

I tried the SUMFS() function in EXCEL 2013 and, of course, it works also. I like the SUMIFS() better because it seems more logical and easier to follow.
The multiplication (*) operator used in the EXCEL 2003 solution is confusing to me (but it works great).

As a second aside:
If I use the SUMIFS() function in EXCEL 2003, it does not work and returns "#NAME?" in he cell.

But, if I use the SUMIFS() function in EXCEL 2013, save it from there as an EXCEL 2003 ".xls" file and then open it in EXCEL 2003, it now works there.

It appears to do this because it somehow automatically changes the "SUMIFS()" function to "_xlfn.SUMIFS()".

So maybe I can have the best of both worlds by composing in EXCEL 2013 when I am in desperately in need of the SUMIFS() function, and then saving it as an EXCEL 2003 ".xls" file when I either want the convenience of EXCEL 2003 or want to give it to a friend who only has EXCEL 2003.

Thanks again,
metsci

[martindwilson]

"_xlfn.SUMIFS()" just lets you know its there if you select that cell and hit enter it will change to #NAME? and will not calculate

[metsci]

Yes!!!

Thanks.

It happened, so I better stick with the EXCEL 2003 solution.

[metsci]

Thanks.

This works in EXCEL 2003

It's hard for me to understand the syntax, but it works.

NOTE: you have a minor typo. In my sample file the "$E" has to be "$F", but then it's fine.

Thanks again,
metsci

[TMS]

It's hard for me to understand the syntax, but it works.

Does that refer to my formula? With regard to the typo, I just picked up the first value field ... missed the reference to column F.

Please Login or Register  to view this content.

It's essentially the same formula as Richard's. However, you can separate the components using commas, instead of asterisks. If you use commas, you need to coerce the TRUE/FALSE values to 1/0 ... which is what the double negative (--) does. The LOWER ensures that the comparison with the entries in column B matches lower case with lower case "buy". Shouldn't be an issue but some comparisons can be case sensitive ... so, belt and braces

You would do well to define your data in terms of Named Ranges to make the references more straightforward and avoid the necessity to edit them. Oh, and beware, you cannot use whole column references in SUMPRODUCT formulae in Excel 2003 ... and you shouldn't in any other version.

Regards, TMS

[metsci]

TMS,

Thanks for the additional insight.

Thanks also for your patience with a "newbie".

metsci

[TMS]

No problem.