Array Formulas

[macandal]

I have lists of investments categorized by years (e.g.: 2001.xls, 2002.xls, 2003.xls, etc). I need to pull certain data from those files to create the reports I need. Pivot Tables and the Consolidate function might be answer to my needs. Besides those functions, I think Array Formulas might also help me isolate the information I need. The final product, I think, would be a combination of Array Formulas, Pivot Tables, and the Consolidate function. Anyway, I wrote my first array formula but it didn't work. I hope someone here can help me.

This is my formula:

{=SUM(IF((B2:B57="Buy"),(C2:C57="XY")))*((F2:F57),(I2:I57))}

What I want it to say (or do) is that if column B says "Buy" and column C says "XY" [that's the condition], then add the amounts in columns F and I that meet that condition. (In other words, if I bought [Buy] shares in a specific company [in this case, XY], how much did I pay for them [column F, price, + column I, commission].)

Can someone please help? Thanks.

 

[Aladin Akyurek]

I have lists of investments categorized by years (e.g.: 2001.xls, 2002.xls, 2003.xls, etc). I need to pull certain data from those files to create the reports I need. Pivot Tables and the Consolidate function might be answer to my needs. Besides those functions, I think Array Formulas might also help me isolate the information I need. The final product, I think, would be a combination of Array Formulas, Pivot Tables, and the Consolidate function. Anyway, I wrote my first array formula but it didn't work. I hope someone here can help me.

This is my formula:

{=SUM(IF((B2:B57="Buy"),(C2:C57="XY")))*((F2:F57),(I2:I57))}

What I want it to say (or do) is that if column B says "Buy" and column C says "XY" [that's the condition], then add the amounts in columns F and I that meet that condition. (In other words, if I bought [Buy] shares in a specific company [in this case, XY], how much did I pay for them [column F, price, + column I, commission].)

Can someone please help? Thanks.

Control+shift+enter, not just enter:

=SUM(IF(B2:B57="Buy",IF(C2:C57="XY",F2:F57+I2:I57)))

 

[macandal]

=SUM(IF(B2:B57="Buy",IF(C2:C57="XY",F2:F57+I2:I57)))

Thanks. That worked like a charm. I guess I almost had it. It was (my version) somewhat reversed.

 

[Aladin Akyurek]

Thanks. That worked like a charm.

You are welcome. Thanks for providing feedback.

I guess I almost had it. It was (my version) somewhat reversed.

The "+" between the two ranges was the issue.

 

[macandal]

I have another issue. What if now I want to say, if column B says "Buy" or "[Whatever other code]" and column C says "XY" [that's the condition], then add the amounts in columns F and I that meet that condition. Basically I'm adding the condition highlighted in red; otherwise the formula is the same as the one provided here that worked. Thanks.

 

[T. Valko]

I have another issue. What if now I want to say, if column B says "Buy" or "[Whatever other code]" and column C says "XY" [that's the condition], then add the amounts in columns F and I that meet that condition. Basically I'm adding the condition highlighted in red; otherwise the formula is the same as the one provided here that worked. Thanks.

Like this...

Array entered**:

=SUM(IF(ISNUMBER(MATCH(B2:B57,{"Buy","Hold"},0)),IF(C2:C57="XY",F2:F57+I2:I57)))

Better to use cells to hold the criteria:

A1 = Buy
B1 = Hold
C1 = XY

=SUM(IF(ISNUMBER(MATCH(B2:B57,A1:B1,0)),IF(C2:C57=C1,F2:F57+I2:I57)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Or, this normally entered version:

=SUMPRODUCT(--ISNUMBER(MATCH(B2:B57,A1:B1,0)),--(C2:C57=C1),F2:F57+I2:I57)

 

[macandal]

Better to use cells to hold the criteria

Can't use cells because the criteria I am using to filter the data I need is not always in the same place.

Can't I just add the other criteria (in my example, the "Or" part in red) to the formula that I already have [=SUM(IF(B2:B57="Buy",IF(C2:C57="XY",F2:F57+I2:I57)))]?

 

[T. Valko]

Like this...

Array entered**:

=SUM(IF(ISNUMBER(MATCH(B2:B57,{"Buy","Hold"},0)),IF(C2:C57="XY",F2:F57+I2:I57)))

Better to use cells to hold the criteria:

A1 = Buy
B1 = Hold
C1 = XY

=SUM(IF(ISNUMBER(MATCH(B2:B57,A1:B1,0)),IF(C2:C57=C1,F2:F57+I2:I57)))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

Or, this normally entered version:

=SUMPRODUCT(--ISNUMBER(MATCH(B2:B57,A1:B1,0)),--(C2:C57=C1),F2:F57+I2:I57)

Can't use cells because the criteria I am using to filter the data I need is not always in the same place.

Can't I just add the other criteria (in my example, the "Or" part in red) to the formula that I already have [=SUM(IF(B2:B57="Buy",IF(C2:C57="XY",F2:F57+I2:I57)))]?

You can also do it like this...

Still array entered:

=SUM(IF(B2:B57={"Buy","Hold"},IF(C2:C57="XY",F2:F57+I2:I57)))

...but the ISNUMBER(MATCH version is more efficient.

=SUM(IF(ISNUMBER(MATCH(B2:B57,{"Buy","Hold"},0)),IF(C2:C57="XY",F2:F57+I2:I57)))