Sums of multiple rows through Offset

[game_federer]

Hi all,

I have that contains sales data for a product P1, P2 or P3 from January to July. Suppose we enter a month and a product into our worksheet. Please help me with a formula using OFFSET that will give the total sales of that product in the given month

 

[Peter Bartholomew]

It is possible to use OFFSET but I would suggest settling for INDEX in this case.

= SUMIFS( INDEX( sales, 0, MATCH( specifiedMonth, months, 0 ) ),
Product, specifiedProduct )

The part of the formula highlighted in magenta is a reference to the sales column corresponding to the specified month. From there your SUMIFS will work fine and is computationally efficient.

 

[bosco_yip]

Try,

In C16, enter :

=SUMIF(A$2:A$10,A16,INDEX(B$2:H$10,0,MATCH(B16,B$1:H$1,0)))

Or,

if you wanted to use OFFSET(), try this :

=SUMIF(A$2:A$10,A16,OFFSET(A$1,1,MATCH(B16,B$1:H$1,0),9))

Regards
Bosco

 

[deciog]

Another way to do without using OFFSET()

=SUMPRODUCT(($A$2:$A$10=$A$16)*($B$1:$H$1=$B$16)*(B2:H10))

Decio

 

[bosco_yip]

Another way to do without using OFFSET()

=SUMPRODUCT(($A$2:$A$10=$A$16)*($B$1:$H$1=$B$16)*(B2:H10))

Decio

Hi Decio,

1] In compare with these 2 formulas :

=SUMIF(A$2:A$10,A16,INDEX(B$2:H$10,0,MATCH(B16,B$1:H$1,0)))

=SUMPRODUCT(($A$2:$A$10=$A$16)*($B$1:$H$1=$B$16)*(B2:H10))

2] This part of formula (highlighted red) is to return the Sum Range

=SUMIF(A$2:A$10,A16,INDEX(B$2:H$10,0,MATCH(B16,B$1:H$1,0)))

=SUMPRODUCT(($A$2:$A$10=$A$16)*($B$1:$H$1=$B$16)*(B2:H10))

* LOOKUP function is always faster than Mathematic calculation

3] Using a VBA Timer in testing of the above formulas

Iterations = 100000

SUMIF+INDEX took 4.67 seconds

SUMPRODUCT took 6.64 seconds

So,

SUMIF+INDEX is faster than SUMPRODUCT around 30% in this example

Regards
Bosco

 

[game_federer]

Hi Decio,

1] In compare with these 2 formulas :

=SUMIF(A$2:A$10,A16,INDEX(B$2:H$10,0,MATCH(B16,B$1:H$1,0)))

=SUMPRODUCT(($A$2:$A$10=$A$16)*($B$1:$H$1=$B$16)*(B2:H10))

2] This part of formula (highlighted red) is to return the Sum Range

=SUMIF(A$2:A$10,A16,INDEX(B$2:H$10,0,MATCH(B16,B$1:H$1,0)))

=SUMPRODUCT(($A$2:$A$10=$A$16)*($B$1:$H$1=$B$16)*(B2:H10))

* LOOKUP function is always faster than Mathematic calculation

3] Using a VBA Timer in testing of the above formulas

Iterations = 100000

SUMIF+INDEX took 4.67 seconds

SUMPRODUCT took 6.64 seconds

So,

SUMIF+INDEX is faster than SUMPRODUCT around 30%

Regards
Bosco

Thank you sir for your comprehensive replies.
Really appreciated!

 

[deciog]

Thank you very much Bosco, this is very useful, I learned one more

Decio

 

[Peter Bartholomew]

The timing differences will increase steadily as the number of months under consideration grows.

There is also a time penalty to be paid for my preferred use of Names (small) and array formulas (noticeable) but those are costs I gladly incur to improve formula transparency and to help control errors.

 

[Haz]

=DSUM(A1:H10, B16, A15:A16)