Summing a column with certain criteria and filtered rows

[elg0007]

I have a large spreadsheet in which I want to sum the values in a column, as long as A) The row is not filtered out, and B) the corresponding value in another column equals something (eg. A1="Approved"). I've played around with SUBTOTAL and SUMPRODUCT and can't get it to work. Ideas?

Thanks!

 

[Joe4]

Welcome to the Board!

I think you should be able to use a SUMIFS or SUMPRODUCT formula. But I think we will need more detail:
1. Can you explain the structure of your data (what is in what column, where your data starts/end, etc)?
2. What is being filtered out (we can use that filter logic in our formulas to exclude those rows)?
3. All the details of the conditions you wish to include.

Thanks

 

[elg0007]

Thanks for the reply! There are about 1800 rows of information. I want to sum the numbers in column R (Working Hours). But I want to be able to filter out results that are marked as "cancelled" or "closed" in Column B if I need to (using "sort and filter") and have them removed from the calculation. Also column AU has about 20 different categories listed and I'd love to get the sum from column R for each AU category.

Eg. For each row that is not filtered out, check which category it belongs to in AU (eg. Reliability, Durability, etc) and add the column R value (working hours) to the total for that category.

Hope that helps.

 

[Joe4]

OK. Here is one way using SUMPRODUCT.

Let's say that you have listed all 20 of your different categories in the range AZ2:AZ21, and you want to list the subtotals of each in range BA2:BA21.
Then just enter this formula in cell BA2 and copy down to BA21:

=SUMPRODUCT(--(B$2:B$1800<>"Cancelled"),--(B$2:B$1800<>"Closed"),--(AU$2:AU$1800=AZ2),--(R$2:R$1800))

That should return what you are looking for.

If there is an issue that some of these rows (2-20) may be filtered out by your filters, then just place your list of categories underneath the last row of data, and adjust the formula accordingly.