Help with altering a formula
 

I am using the following formula as part of my data source for a chart and it
works perfectly. It sums column D, rows 2 through 20K where the value in
column A, rows 2 through 20K = cell G2 and where the value in column E, rows
2 through 20K = 1. The SUBTOTAL allows for any filtering on my source tab
(AAT_Raw_Data) to dynamically get factored in.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(AAT_Raw_Data!$D$2:AA T_Raw_Data!$D$20000,ROW(AAT_Raw_Data!$D$2:AAT_Raw_ Data!$D$20000)-ROW(AAT_Raw_Data!$D$2),0,1)),--(AAT_Raw_Data!$A$2:AAT_Raw_Data!$A$20000=G2),--(AAT_Raw_Data!$E$2:AAT_Raw_Data!$E$20000=1))

I need to alter this a bit. I need a count of cells in column B, rows 2
through 20K where the value in column A, rows 2 through 20K = cell G2 and
where the value in column D, rows 2 through 20K = 180. I still need to
factor in any filtering that might happen on tab (AAT_Raw_Data).

It seems the only real difference is that I need a count instead of a sum. A
couple of the column references are changing, but that's not what I'm stuck
on.

Can someone help me with this?

Help with altering a formula
 

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(AAT_Raw_Data!B2:B200 00,ROW(AAT_Raw_Data!B2:B20000)-ROW(AAT_Raw_Data!B2),0,1)),--(AAT_Raw_Data!A2:A20000=G2),--(AAT_Raw_Data!D2:D20000=180))

--
Biff
Microsoft Excel MVP


"cherman" wrote in message
...
I am using the following formula as part of my data source for a chart and
it
works perfectly. It sums column D, rows 2 through 20K where the value in
column A, rows 2 through 20K = cell G2 and where the value in column E,
rows
2 through 20K = 1. The SUBTOTAL allows for any filtering on my source tab
(AAT_Raw_Data) to dynamically get factored in.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(AAT_Raw_Data!$D$2:AA T_Raw_Data!$D$20000,ROW(AAT_Raw_Data!$D$2:AAT_Raw_ Data!$D$20000)-ROW(AAT_Raw_Data!$D$2),0,1)),--(AAT_Raw_Data!$A$2:AAT_Raw_Data!$A$20000=G2),--(AAT_Raw_Data!$E$2:AAT_Raw_Data!$E$20000=1))

I need to alter this a bit. I need a count of cells in column B, rows 2
through 20K where the value in column A, rows 2 through 20K = cell G2 and
where the value in column D, rows 2 through 20K = 180. I still need to
factor in any filtering that might happen on tab (AAT_Raw_Data).

It seems the only real difference is that I need a count instead of a sum.
A
couple of the column references are changing, but that's not what I'm
stuck
on.

Can someone help me with this?

Help with altering a formula
 

Worked like a c-h-a-r-m!

Much thanks!


"T. Valko" wrote:

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(AAT_Raw_Data!B2:B200 00,ROW(AAT_Raw_Data!B2:B20000)-ROW(AAT_Raw_Data!B2),0,1)),--(AAT_Raw_Data!A2:A20000=G2),--(AAT_Raw_Data!D2:D20000=180))

--
Biff
Microsoft Excel MVP


"cherman" wrote in message
...
I am using the following formula as part of my data source for a chart and
it
works perfectly. It sums column D, rows 2 through 20K where the value in
column A, rows 2 through 20K = cell G2 and where the value in column E,
rows
2 through 20K = 1. The SUBTOTAL allows for any filtering on my source tab
(AAT_Raw_Data) to dynamically get factored in.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(AAT_Raw_Data!$D$2:AA T_Raw_Data!$D$20000,ROW(AAT_Raw_Data!$D$2:AAT_Raw_ Data!$D$20000)-ROW(AAT_Raw_Data!$D$2),0,1)),--(AAT_Raw_Data!$A$2:AAT_Raw_Data!$A$20000=G2),--(AAT_Raw_Data!$E$2:AAT_Raw_Data!$E$20000=1))

I need to alter this a bit. I need a count of cells in column B, rows 2
through 20K where the value in column A, rows 2 through 20K = cell G2 and
where the value in column D, rows 2 through 20K = 180. I still need to
factor in any filtering that might happen on tab (AAT_Raw_Data).

It seems the only real difference is that I need a count instead of a sum.
A
couple of the column references are changing, but that's not what I'm
stuck
on.

Can someone help me with this?


.

Help with altering a formula
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"cherman" wrote in message
...
Worked like a c-h-a-r-m!

Much thanks!


"T. Valko" wrote:

Try this:

=SUMPRODUCT(SUBTOTAL(3,OFFSET(AAT_Raw_Data!B2:B200 00,ROW(AAT_Raw_Data!B2:B20000)-ROW(AAT_Raw_Data!B2),0,1)),--(AAT_Raw_Data!A2:A20000=G2),--(AAT_Raw_Data!D2:D20000=180))

--
Biff
Microsoft Excel MVP


"cherman" wrote in message
...
I am using the following formula as part of my data source for a chart
and
it
works perfectly. It sums column D, rows 2 through 20K where the value
in
column A, rows 2 through 20K = cell G2 and where the value in column E,
rows
2 through 20K = 1. The SUBTOTAL allows for any filtering on my source
tab
(AAT_Raw_Data) to dynamically get factored in.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(AAT_Raw_Data!$D$2:AA T_Raw_Data!$D$20000,ROW(AAT_Raw_Data!$D$2:AAT_Raw_ Data!$D$20000)-ROW(AAT_Raw_Data!$D$2),0,1)),--(AAT_Raw_Data!$A$2:AAT_Raw_Data!$A$20000=G2),--(AAT_Raw_Data!$E$2:AAT_Raw_Data!$E$20000=1))

I need to alter this a bit. I need a count of cells in column B, rows 2
through 20K where the value in column A, rows 2 through 20K = cell G2
and
where the value in column D, rows 2 through 20K = 180. I still need to
factor in any filtering that might happen on tab (AAT_Raw_Data).

It seems the only real difference is that I need a count instead of a
sum.
A
couple of the column references are changing, but that's not what I'm
stuck
on.

Can someone help me with this?


.