subtotal and sumif
 

Ive want to summarise data in a column but with given parameters.

I list the data and i use autofill for the columns. By then using
subtotal(109) i manage to get totals. But i want totals that also utilizes
the function of sumif. For example: I would like to get the total of the O
column but only for the ones that have "regular" beside them.


M N O
1 x Temp 25
2 y Regular 18
3 z Temp 21
4 x Regular 34
5 x Regular 19

The answer here should be 18+34+19
Sumif shows this easy but i want to hide certain colums using autofill by
the column M and then use subtotal but with sumif to get all regular values
that are shown.

I tried this but it didnt work:
=SUMPRODUCT(SUBTOTAL(3,OFFSET($N$14:$N$73,row($N$1 4:$N$73)-MIN(ROW($N$14:$N$73)),0,1)),--($N$14:$N$73="regular"),O$17:O$73)

It just returns that 3,OFFSET is an error?

subtotal and sumif
 

Maybe you're using a version of Excel that uses a semi-colon instead of
a comma as a separator. Try replacing the commas with semi-colons.
Also, the ranges need to be the same size.

Hope this helps!

In article ,
Pete wrote:

Ive want to summarise data in a column but with given parameters.

I list the data and i use autofill for the columns. By then using
subtotal(109) i manage to get totals. But i want totals that also utilizes
the function of sumif. For example: I would like to get the total of the O
column but only for the ones that have "regular" beside them.


M N O
1 x Temp 25
2 y Regular 18
3 z Temp 21
4 x Regular 34
5 x Regular 19

The answer here should be 18+34+19
Sumif shows this easy but i want to hide certain colums using autofill by
the column M and then use subtotal but with sumif to get all regular values
that are shown.

I tried this but it didnt work:
=SUMPRODUCT(SUBTOTAL(3,OFFSET($N$14:$N$73,row($N$1 4:$N$73)-MIN(ROW($N$14:$N$73
)),0,1)),--($N$14:$N$73="regular"),O$17:O$73)

It just returns that 3,OFFSET is an error?

subtotal and sumif
 

Thanks! That helped me alot!

"Domenic" wrote:

Maybe you're using a version of Excel that uses a semi-colon instead of
a comma as a separator. Try replacing the commas with semi-colons.
Also, the ranges need to be the same size.

Hope this helps!

In article ,
Pete wrote:

Ive want to summarise data in a column but with given parameters.

I list the data and i use autofill for the columns. By then using
subtotal(109) i manage to get totals. But i want totals that also utilizes
the function of sumif. For example: I would like to get the total of the O
column but only for the ones that have "regular" beside them.


M N O
1 x Temp 25
2 y Regular 18
3 z Temp 21
4 x Regular 34
5 x Regular 19

The answer here should be 18+34+19
Sumif shows this easy but i want to hide certain colums using autofill by
the column M and then use subtotal but with sumif to get all regular values
that are shown.

I tried this but it didnt work:
=SUMPRODUCT(SUBTOTAL(3,OFFSET($N$14:$N$73,row($N$1 4:$N$73)-MIN(ROW($N$14:$N$73
)),0,1)),--($N$14:$N$73="regular"),O$17:O$73)

It just returns that 3,OFFSET is an error?