Solved Adding IF statement that multiplies to subtotal command

May 21, 2018 at 11:09:02
Specs: Windows 7
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?


See More: Adding IF statement that multiplies to subtotal command

Report •

✔ Best Answer
May 23, 2018 at 09:09:04
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 physical size of the sheet, but it wouldn't have to increase the visual size.

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.

If you 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 cell of 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



#1
May 21, 2018 at 11:47:38
I'll admit it. I'm confused.

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

What 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


Report •

#2
May 21, 2018 at 11:54:11
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,791

So, 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


Report •

#3
May 21, 2018 at 13:15:42
First, a posting tip:

Please click on the How-To link at 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


Report •

Related Solutions

#4
May 21, 2018 at 13:46:14
       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.


Report •

#5
May 23, 2018 at 06:25:13
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


Report •

#6
May 23, 2018 at 06:35:36
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.

Report •

#7
May 23, 2018 at 06:53:25
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*D2

      A          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


Report •

#8
May 23, 2018 at 07:05:59
That's what I was hoping to avoid by creating extra columns. It's already a huge spreadsheet.

Report •

#9
May 23, 2018 at 08:27:11
Are you only filtering by a single person each time? e.g. in my example above would the subtotal only be for Tom or Sue but never Tom and Sue?

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


Report •

#10
May 23, 2018 at 08:29:53
No, I may be filtering multiple ways across different columns (i.e. job title and account number or sometimes just the account number).

Report •

#11
May 23, 2018 at 09:09:04
✔ 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 physical size of the sheet, but it wouldn't have to increase the visual size.

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.

If you 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 cell of 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


Report •

#12
May 23, 2018 at 11:36:17
Thanks! I'll work with hiding the columns and adding the subtotal onto a visible cell.

Report •

Ask Question