CSE-function version of SumIf

[jberg123]

In column A: week numbers
In cell U4: a week number
In column I: if the trade was a B (buy) or S (sell)
In cell V3: the letter B

the CSE-formula {=SUM(IF($A$2:$A$111=$U4,IF($I$2:$I$111=V$3,1,0),0))} will give the number of B (buys) in a given week (specified by week number).

Using this I create a table with the number of buy order during a range of different weeks.

Now, instead, I would like to sum the total number shares bought during each week. The volume of bought shares for each order is in column J.

How can I do this?

My current CSE-function is a bit like a 2-condition CountIf. What I need to turn it into is a 2-condition SumIf.

Any ideas?

Many thanks

 

[T. Valko]

In column A: week numbers
In cell U4: a week number
In column I: if the trade was a B (buy) or S (sell)
In cell V3: the letter B

the CSE-formula {=SUM(IF($A$2:$A$111=$U4,IF($I$2:$I$111=V$3,1,0),0))} will give the number of B (buys) in a given week (specified by week number).

Using this I create a table with the number of buy order during a range of different weeks.

Now, instead, I would like to sum the total number shares bought during each week. The volume of bought shares for each order is in column J.

How can I do this?

My current CSE-function is a bit like a 2-condition CountIf. What I need to turn it into is a 2-condition SumIf.

Any ideas?

Many thanks

No need for a CSE formula.

Try one of these...

This one will work in any version of Excel:

=SUMPRODUCT(--(A2:A111=U4),--(I2:I111=V3),J2:J111)

This one will work in excel 2007 or later:

=SUMIFS(J2:J111,A2:A111,U4,I2:I111,V3)

 

[Aladin Akyurek]

In column A: week numbers
In cell U4: a week number
In column I: if the trade was a B (buy) or S (sell)
In cell V3: the letter B

the CSE-formula {=SUM(IF($A$2:$A$111=$U4,IF($I$2:$I$111=V$3,1,0),0))} will give the number of B (buys) in a given week (specified by week number).

Using this I create a table with the number of buy order during a range of different weeks.

Now, instead, I would like to sum the total number shares bought during each week. The volume of bought shares for each order is in column J.

How can I do this?

My current CSE-function is a bit like a 2-condition CountIf. What I need to turn it into is a 2-condition SumIf.

Any ideas?

Many thanks

The formula would become something like:

Control+shift+enter...

=SUM(
    IF($A$2:$A$111=$U4,
    IF($I$2:$I$111=V$3,
      $J$2:$J$111)))

It's a good idea to switch to COUNTIFS and SUMIFS if you are on Excel 2007 or later.