If, Countif, Subtotal...?

Can't seem to get this one right:

I basically need a formula to filter the rows that meet filter criteria in one sheet AND THEN to sum/subtotal the results of the items that have been filtered.

In other words...

If the word "Yes" appears in Sheet1, column A, row 1, take the monetary value of Sheet1, column B, row 1 and include it in a total shown on another sheet.

To produce a total in Sheet2, I have currently used...

SUBTOTAL(9,Sheet1!B:B)

... which works fine as long as I have already filtered the results in column A on Sheet1.

However, as soon as I remove the filter on Sheet1, the subtotal/sum alters to include all values and not just those meeting the "Yes" criteria, above.

If this is easy stuff, sorry!

Thanks, in anticipation, for any help out there.

Perfect!

Works a treat, as long as a range is included, as distinct to a whole column (i.e. A1:A100 and not just A:A).

Thanks, oldchippy. You're a star!

Kinds regards...

Richard

Thanks for that.

All the best...

Richard