Conditional Subtotal in table
 

I have a spreadsheet with several different years of data in one row (last
year, this year, original plan).

I can create a table to subtotal by different criteria, but I'd like to add
a subtotal based on the text in another row.

Example: Sales at Cost are in column E. The year (TY, LY) is in column D.

Is it possible to subtotal only visible cells in column E when column D="TY"?

Conditional Subtotal in table
 

if you are looking for one conditional use

=sumif(d2:d100,"TY",f2:f100)
assuming your figures are in column F

if you are looking for a multiple conditionals use

=sumproduct(--(e2:e100="Sales at Cost")*(d2:d100="TY");(f2:f100))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Esh" escreveu:

I have a spreadsheet with several different years of data in one row (last
year, this year, original plan).

I can create a table to subtotal by different criteria, but I'd like to add
a subtotal based on the text in another row.

Example: Sales at Cost are in column E. The year (TY, LY) is in column D.

Is it possible to subtotal only visible cells in column E when column D="TY"?

Conditional Subtotal in table
 

Thanks for the reply! The SUMIF does what I need it to do, but I need it to
only SUM visible cells, since the way I get the data, LY/TY are in the same
row.

"Marcelo" wrote:

if you are looking for one conditional use

=sumif(d2:d100,"TY",f2:f100)
assuming your figures are in column F

if you are looking for a multiple conditionals use

=sumproduct(--(e2:e100="Sales at Cost")*(d2:d100="TY");(f2:f100))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Esh" escreveu:

I have a spreadsheet with several different years of data in one row (last
year, this year, original plan).

I can create a table to subtotal by different criteria, but I'd like to add
a subtotal based on the text in another row.

Example: Sales at Cost are in column E. The year (TY, LY) is in column D.

Is it possible to subtotal only visible cells in column E when column D="TY"?

Conditional Subtotal in table
 

a pivot table could help you?


--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Esh" escreveu:

Thanks for the reply! The SUMIF does what I need it to do, but I need it to
only SUM visible cells, since the way I get the data, LY/TY are in the same
row.

"Marcelo" wrote:

if you are looking for one conditional use

=sumif(d2:d100,"TY",f2:f100)
assuming your figures are in column F

if you are looking for a multiple conditionals use

=sumproduct(--(e2:e100="Sales at Cost")*(d2:d100="TY");(f2:f100))

hth
--
regards from Brazil
Thanks in advance for your feedback.
Marcelo



"Esh" escreveu:

I have a spreadsheet with several different years of data in one row (last
year, this year, original plan).

I can create a table to subtotal by different criteria, but I'd like to add
a subtotal based on the text in another row.

Example: Sales at Cost are in column E. The year (TY, LY) is in column D.

Is it possible to subtotal only visible cells in column E when column D="TY"?