How do I do a sumif function not including hidden rows?
 

Pease, I need some help!

I want to sum a column with a criteria (in this case another column with
selective creiteria = 1) but I dont want hidden rows to be included. It
should be a function that is a subtotal yet taking in consideration a
criteria. Any ideas? Thanks a lot!

How do I do a sumif function not including hidden rows?
 

Assuming that they are hidden via filter, this sums the values in E when C
is filtered, and D = Assigned

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),--($D$2:$D$1
9="Assigned"),$E$2:$E$19)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Verlaesslichkeit" wrote in
message ...
Pease, I need some help!

I want to sum a column with a criteria (in this case another column with
selective creiteria = 1) but I dont want hidden rows to be included. It
should be a function that is a subtotal yet taking in consideration a
criteria. Any ideas? Thanks a lot!

How do I do a sumif function not including hidden rows?
 

Something didnt work... Ill try illustrating it better

A B C
0 15 0
1 15 1
0 18 0
1 18 1
1 21 0
0 23 0

Now I want the sumif when column A is filtered to 1 and column C =1 . In
this way, the result should be 15 + 18 = 33





"Bob Phillips" wrote:

Assuming that they are hidden via filter, this sums the values in E when C
is filtered, and D = Assigned

=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),--($D$2:$D$1
9="Assigned"),$E$2:$E$19)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Verlaesslichkeit" wrote in
message ...
Pease, I need some help!

I want to sum a column with a criteria (in this case another column with
selective creiteria = 1) but I dont want hidden rows to be included. It
should be a function that is a subtotal yet taking in consideration a
criteria. Any ideas? Thanks a lot!

How do I do a sumif function not including hidden rows?
 

It simply needed adjusting the supplied formula to your data

=SUMPRODUCT(SUBTOTAL(3,OFFSET(A1,ROW($A$2:$A$19)-ROW($A$1),,1)),--(C$2:$C$19
=1),$B$2:$B$19)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Verlaesslichkeit" wrote in
message ...
Something didnt work... Ill try illustrating it better

A B C
0 15 0
1 15 1
0 18 0
1 18 1
1 21 0
0 23 0

Now I want the sumif when column A is filtered to 1 and column C =1 . In
this way, the result should be 15 + 18 = 33





"Bob Phillips" wrote:

Assuming that they are hidden via filter, this sums the values in E when
C
is filtered, and D = Assigned


=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),--($D$2:$D$1
9="Assigned"),$E$2:$E$19)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Verlaesslichkeit" wrote in
message ...
Pease, I need some help!

I want to sum a column with a criteria (in this case another column
with
selective creiteria = 1) but I dont want hidden rows to be included.
It
should be a function that is a subtotal yet taking in consideration a
criteria. Any ideas? Thanks a lot!

How do I do a sumif function not including hidden rows?
 

If your data is truly zeros and ones as in your example, you could add a
column D with the formula =A1*B1*C1 and sum column D. If your sample data
is a simplified version of the actual table, the formula could be changed to
=(A1="criteria")*B1*(C1="criteria"). This answer will be the same whether
the column is filtered or not.

--
Carlos

"Verlaesslichkeit" wrote in
message ...
Something didnt work... Ill try illustrating it better

A B C
0 15 0
1 15 1
0 18 0
1 18 1
1 21 0
0 23 0

Now I want the sumif when column A is filtered to 1 and column C =1 . In
this way, the result should be 15 + 18 = 33





"Bob Phillips" wrote:

Assuming that they are hidden via filter, this sums the values in E when
C
is filtered, and D = Assigned


=SUMPRODUCT(SUBTOTAL(3,OFFSET(C1,ROW($C$2:$C$19)-ROW($C$1),,1)),--($D$2:$D$1
9="Assigned"),$E$2:$E$19)

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Verlaesslichkeit" wrote in
message ...
Pease, I need some help!

I want to sum a column with a criteria (in this case another column
with
selective creiteria = 1) but I dont want hidden rows to be included.
It
should be a function that is a subtotal yet taking in consideration a
criteria. Any ideas? Thanks a lot!