Subtotal question
 

Hi

I have a large sheet with production data on. Col E contains shift
letter, A, B, or C and Col Z contains average producivity. I have

=SUBTOTAL(1,Z5:Z800)

at the head of col Z so that when I filter col E for each shift the
average for that shift is displayed at the top of col Z.

Is it possible to have something like:

=IF(E5:E800="A",SUBTOTAL(1,Z5:Z800))
=IF(E5:E800="B",SUBTOTAL(1,Z5:Z800))
=IF(E5:E800="B",SUBTOTAL(1,Z5:Z800))

In 3 cells to hopefully display the results for each shift without
having to filter col E allowing direct shift to shift comparison.

Hope that makes sense

Thanks in anticipation
Martin

Subtotal question
 

How about

=SUMIF(E5:E800,"A",Z5:Z800)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

" wrote in message
...
Hi

I have a large sheet with production data on. Col E contains shift
letter, A, B, or C and Col Z contains average producivity. I have

=SUBTOTAL(1,Z5:Z800)

at the head of col Z so that when I filter col E for each shift the
average for that shift is displayed at the top of col Z.

Is it possible to have something like:

=IF(E5:E800="A",SUBTOTAL(1,Z5:Z800))
=IF(E5:E800="B",SUBTOTAL(1,Z5:Z800))
=IF(E5:E800="B",SUBTOTAL(1,Z5:Z800))

In 3 cells to hopefully display the results for each shift without
having to filter col E allowing direct shift to shift comparison.

Hope that makes sense

Thanks in anticipation
Martin

Subtotal question
 

Hi,

Id do it another way.

Put 3 formula at the top of your range like this

=SUMPRODUCT(($E$5:$E$800="A")*($Z$5:$Z$800))

The other 2 being for B & C shift and you will get a total for each no
matter what filter you apply.

Mike

" wrote:

Hi

I have a large sheet with production data on. Col E contains shift
letter, A, B, or C and Col Z contains average producivity. I have

=SUBTOTAL(1,Z5:Z800)

at the head of col Z so that when I filter col E for each shift the
average for that shift is displayed at the top of col Z.

Is it possible to have something like:

=IF(E5:E800="A",SUBTOTAL(1,Z5:Z800))
=IF(E5:E800="B",SUBTOTAL(1,Z5:Z800))
=IF(E5:E800="B",SUBTOTAL(1,Z5:Z800))

In 3 cells to hopefully display the results for each shift without
having to filter col E allowing direct shift to shift comparison.

Hope that makes sense

Thanks in anticipation
Martin

Subtotal question
 

This is a great link for things pertaining to the sumproduct:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards,
Ryan---

--
RyGuy


"Bob Phillips" wrote:

How about

=SUMIF(E5:E800,"A",Z5:Z800)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

" wrote in message
...
Hi

I have a large sheet with production data on. Col E contains shift
letter, A, B, or C and Col Z contains average producivity. I have

=SUBTOTAL(1,Z5:Z800)

at the head of col Z so that when I filter col E for each shift the
average for that shift is displayed at the top of col Z.

Is it possible to have something like:

=IF(E5:E800="A",SUBTOTAL(1,Z5:Z800))
=IF(E5:E800="B",SUBTOTAL(1,Z5:Z800))
=IF(E5:E800="B",SUBTOTAL(1,Z5:Z800))

In 3 cells to hopefully display the results for each shift without
having to filter col E allowing direct shift to shift comparison.

Hope that makes sense

Thanks in anticipation
Martin

Subtotal question
 

Yeah, I know it is, but I offered SUMIF!

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

"ryguy7272" wrote in message
...
This is a great link for things pertaining to the sumproduct:
http://www.xldynamic.com/source/xld.SUMPRODUCT.html

Regards,
Ryan---

--
RyGuy


"Bob Phillips" wrote:

How about

=SUMIF(E5:E800,"A",Z5:Z800)


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my
addy)

" wrote in message
...
Hi

I have a large sheet with production data on. Col E contains shift
letter, A, B, or C and Col Z contains average producivity. I have

=SUBTOTAL(1,Z5:Z800)

at the head of col Z so that when I filter col E for each shift the
average for that shift is displayed at the top of col Z.

Is it possible to have something like:

=IF(E5:E800="A",SUBTOTAL(1,Z5:Z800))
=IF(E5:E800="B",SUBTOTAL(1,Z5:Z800))
=IF(E5:E800="B",SUBTOTAL(1,Z5:Z800))

In 3 cells to hopefully display the results for each shift without
having to filter col E allowing direct shift to shift comparison.

Hope that makes sense

Thanks in anticipation
Martin