if statement on a pivot table

[cware]

I'm trying to find the difference of two columns of data (only on the
subtotal row) created with a pivot table, instead of the "total" that the
table gives me. I thought I would be able to put in an "if" statement,
sopmething to the effect of "if column B includes the text "total", then
subtract column E from column F. I don't want it on all rows, only the
subtotal rows, which all include the text "total".

Does anyone have an idea on how I can accomplish this?

Thanks!
Cathy

 

[tsides]

if column B includes the text "total" exactly, then the following
formula would work (copied to all rows):
=IF(B1="total",F1-E1,"")

however, if by "contains" you mean that the cell in B3 might have the
word "SubTotal" or "Sub Total" or even "Total widgets", then you will
need:
=IF(ISERROR(FIND("total",LOWER(B1))),"",F1-E1)

 

[cware]

THANK YOU VERY MUCH.....At this time I don't know what it means, but when
copying it in, it works. I'll figure it out later so that I can apply in
other areas as well.

Thanks again.

Cathy

 

[tsides]

LOWER(B1) takes the data in B1 and puts it all in lower case letters.

FIND("total",LOWER(B1)) gets the character number in the contents of
the lower-case version of cell B1 where the text "total" starts. If
B1 contains "SubTotal" then this will return a 4. Notice that the
function translated "SubTotal" to "subtotal" first, so that it could
find "total" in there.

If B1 does not contain "total", then the FIND function will return an
error. So the whole IF statement basicaly says if there's an error
(and it's not a total row) then fill the cell with an empty string,
otherwise, fill the cell with the F1-E1 formula that you wanted.