combine SUBTOTAL with IF

[tspears]

Hello All!

Is there a way to combine the SUBTOTAL function with an IF statement?

I am using SUBTOTAL functions to count, sum and average my filtered data. But, i would also like to count, sum and average a *subset* of that filtered data when it meets a certain criteria.

Any ideas you can offer, I really appreciate!!

 

[DaleWatson123321]

Hi tspears,

"Keywords" you used ("subset" and "criteria&quot indicate that you should become aware of the "next level" of Excel's "Data Filter" options.

This next level is: Data - Filter - Advanced Filter.

Many Excel users have attempted to utilized this "Advanced Filter" level, only to become discouraged because of running into two BUGS in the form of "FALSE" ERROR messages.

One message informs the user that it's NOT possible to extract data to a SEPARATE sheet. The other says you can NOT place your "criteria" on a SEPARATE sheet. Both these are FALSE - i.e. it IS possible. You only need to record and utilize the VBA commands for extracting the data, rather than attempting to do so manually.

There are also database functions that can be used with the same "criteria". These include DCOUNTA, DSUM, DAVG.

You can therefore provide a "matrix" or summary of statistics, for example of: Types of Products Produced or Sold - By Month, By Quarter, By Year, or whatever. And this summary can be on a SEPARATE sheet. Or, you could have multiple reports - each on its own sheet, depending on the complexity of your data and your reporting needs.

Using the same "criteria", you can extract the "records behind the totals", to produce "detailed" reports.

If you would like example files, I can email you a variety of such files - that should prove useful in learning this POWERFUL component of Excel.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca