I need a formula that gives me a sum based on an IF formula with filters. I am trying to subtotal filtered data that will multiply the value between two cells if the value is greater than 0.

For example, I have rows B4:B152 that will be multiplied with L4:L152 if >0. Then I want it to subtotal because there are already filters in place.

Is this possible?

✔ Best Answer

re: " That's what I was hoping to avoid by creating extra columns. It's already a huge spreadsheet."You could hide the Helper Columns. Yes, it would increase the

physicalsize of the sheet, but it wouldn't have to increase thevisualsize.Beyond that...

This is a little tough without your actual spreadsheet layout and data to work with.

One possible solution would be to use SUMPRODUCT as long as you could pass the "filter criteria" to the SUMPRODUCT function.

Ifyou were filtering on a single entity, you could capture the criteria and pass that to the SUMPRODUCT function. The SUMPRODUCT wouldn't actually be subtotaling the filtered data, it would be working on the entire database but only summing those cells that met the filtering criteria.For example, in my example above, this formula would capture the value in

the last visible cellof the filtered data, e.g. either Tom or Sue:=LOOKUP(2,1/((SUBTOTAL(3,OFFSET(A2:A5,ROW(A2:A5)-MIN(ROW(A2:A5)),0,1)))*(1-ISBLANK(A2:A5))),A2:A5)

(Note: I did not write that formula. I stole it and adapted it.)

Let's say that that formula was in H1. SUMPRODUCT could use the result of that formula to create the subtotal for that person:

=SUMPRODUCT(C2:C5,E2:E5*(A2:A5=H1))

That should return the subtotal for "Net Acct 1" for Tom or Sue, depending on which name you filtered on.

If you didn't want to use an extra cell, you could combine the LOOKUP function with the SUMPRODUCT function:

=SUMPRODUCT(C2:C5,E2:E5*(A2:A5=LOOKUP(2,1/((SUBTOTAL(3,OFFSET(A2:A5,ROW(A2:A5)-MIN(ROW(A2:A5)),0,1)))*(1-ISBLANK(A2:A5))),A2:A5)))

The obvious problem is that you are only passing one criteria to the SUMPRODUCT.

It's possible that with some more playing around we could come up with a way to nest multiple SUMPRODUCT functions and pass multiple criteria. i.e the last visible cell of more than one column, but I'm not sure of that (yet). My concern is that without knowing exactly what your work process is and exactly how your data is laid out, I may spend a lot of time get my "example data" to work only to find that my suggestion won't work for you or with your data.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

I'll admit it. I'm confused.

multiply the value between two cells if the value is greater than 0What value?

I have rows B4:B152 that will be multiplied with L4:L152 if >0.If what is greater than 0?

Oh wait, that won't matter, since anything multiplied by 0 equals 0 and wouldn't impact the subtotal anyway.

I think we (at least me) need a little bit more of an explanation.

Perhaps some example data would help.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Sorry! Here's sample data: B L

4 100% 15,030

5 100% 15,687

6 0% 18,004

7 0% 19,712

8 100% 18,266

9 67% 14,791So, if there's a percentage greater than 0 in column B, I want it to multiply each line (B#*L#). Then, I want to subtotal the amount multiplied because I have filters in place. I hope that makes sense.

message edited by maeree7578

First, a posting tip: Please click on the

How-To linkat the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.That said, I'm still confused. Maybe you need to explain what it is that you want to subtotal.

If you multiply Rows 4, 5, 8 and 9 (non-zero percentages) , the "subtotal" will be 58,892.97.

If you multiply Rows 4:9 the total will also be 58,892.97 because Rows 6 & 7 will return 0.

I don't see why you need an If statement to decide which rows to multiply.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

A B C % in Acct 1 % in Acct 2 Gross 3 100.0% 0.0% 22,700 17 56.7% 43.3% 15,313 37 40.0% 60.0% 98,811 87 48.0% 52.0% 10,968 108 20.0% 80.0% 25,296 156 Total Gross Acct 1 ???? 157 Total Gross Acct 2 ????The row #'s are accurate to my actual data. It's already filtered. Since it's filtered, I know I need to do a SUBTOTAL command or something like that so each time I filter a different way, I can get the total of only what's visible.

For the total, I want the percentage multiplied by the gross for Acct 1 and the same for Acct 2. But, each time I apply a filter for different people (which you can't see), I'd like the total to change based on the filter. The ????'s above are what I'm trying to figure out.

Thanks for being so patient and helpful.

You mentioned the SUBTOTAL function. Have you tried it? http://www.microknowledge.com/subto...

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03

Yes, I already have a subtotal command to add up column c, but I want to multiply column a with column c and add up that total separate from column b with column c. So, total of Gross Acct 1 would be one cell and the other cell would be Gross Acct 2.

Add 2 Helper Columns to calculate the Net and then SUBTOTAL the Helper Columns. In this case, SUBTOTAL Columns E for Net Acct 1 and F for Net Acct 2. E2: =B2*D2

F2: =C2*D2A B C D E F 1 Name % In Acct 1 % in Acct 2 Gross Net Acct 1 Net Acct 2 2 Tom 100% 0% 100 100 0 3 Sue 50% 16% 200 100 16 4 Tom 12% 25% 600 72 150 5 Sue 10% 90% 50 5 45

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

That's what I was hoping to avoid by creating extra columns. It's already a huge spreadsheet.

Are you only filtering by a single person each time? e.g. in my example above would the subtotal only be for Tom orSue but never TomandSue?

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

No, I may be filtering multiple ways across different columns (i.e. job title and account number or sometimes just the account number).

re: " That's what I was hoping to avoid by creating extra columns. It's already a huge spreadsheet."You could hide the Helper Columns. Yes, it would increase the

physicalsize of the sheet, but it wouldn't have to increase thevisualsize.Beyond that...

This is a little tough without your actual spreadsheet layout and data to work with.

One possible solution would be to use SUMPRODUCT as long as you could pass the "filter criteria" to the SUMPRODUCT function.

Ifyou were filtering on a single entity, you could capture the criteria and pass that to the SUMPRODUCT function. The SUMPRODUCT wouldn't actually be subtotaling the filtered data, it would be working on the entire database but only summing those cells that met the filtering criteria.For example, in my example above, this formula would capture the value in

the last visible cellof the filtered data, e.g. either Tom or Sue:=LOOKUP(2,1/((SUBTOTAL(3,OFFSET(A2:A5,ROW(A2:A5)-MIN(ROW(A2:A5)),0,1)))*(1-ISBLANK(A2:A5))),A2:A5)

(Note: I did not write that formula. I stole it and adapted it.)

Let's say that that formula was in H1. SUMPRODUCT could use the result of that formula to create the subtotal for that person:

=SUMPRODUCT(C2:C5,E2:E5*(A2:A5=H1))

That should return the subtotal for "Net Acct 1" for Tom or Sue, depending on which name you filtered on.

If you didn't want to use an extra cell, you could combine the LOOKUP function with the SUMPRODUCT function:

=SUMPRODUCT(C2:C5,E2:E5*(A2:A5=LOOKUP(2,1/((SUBTOTAL(3,OFFSET(A2:A5,ROW(A2:A5)-MIN(ROW(A2:A5)),0,1)))*(1-ISBLANK(A2:A5))),A2:A5)))

The obvious problem is that you are only passing one criteria to the SUMPRODUCT.

It's possible that with some more playing around we could come up with a way to nest multiple SUMPRODUCT functions and pass multiple criteria. i.e the last visible cell of more than one column, but I'm not sure of that (yet). My concern is that without knowing exactly what your work process is and exactly how your data is laid out, I may spend a lot of time get my "example data" to work only to find that my suggestion won't work for you or with your data.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

Thanks! I'll work with hiding the columns and adding the subtotal onto a visible cell.

Ask Your Question

Weekly Poll

Do you think Salesforce should have bought Slack?

Discuss in The Lounge

Poll History