Multiplying Ranges of Cells IF (Possible SumProduct??)

[spectacularstuff]

Hello,

I have an issue I cannot seem to figure out. I would like to program this in excel vs. a macro due to the amount of adjustments I make to the sheet every week. I need to add something on to my current programming.

I currently have two working columns and 1 working functional cell in the following question.
Column E
Column F
Cell F46


Column E is for people to put an X in to tell Excel to add up the value in the cell in the same row in Column F.

In the below example cell F46 would add up $55.00 + $145.00 because someone put an X in the adjacent cell in Column E.

(This has been shortened. There are 41 rows in the real programming)

E       F
     $125.00
X    $ 55.00
     $125.00
     $150.00
     $600.00
X    $145.00
     $ 75.00

F46 =$200.00

I use the following code in order to accomplish this:
(The below code also adds 7% tax if an X is in the appropriate spot)

=IF(SUMPRODUCT(--($E$4:$E6="X")),(SUMPRODUCT(--($E$4:$E45="X"),F$4:F45)+SUMPRODUCT(--($E$4:$E45="X"),F$4:F45)*0.07),SUMPRODUCT(--($E$4:$E45="X"),F$4:F45))

I want to add another Column (Column D) that I can enter a number in such as 2 or 4 or 9, etc etc etc and excel will then look to see if there is an X in the range of cells for column E.
If there is an X, it will then look to see if there is a number in range of cells for column D.
If there is a number in column D then it will mulitply that number times the value in the cell in the same row for column F as follows:
If there is not a number in Column D, it will automatically presume 1.

(This has been shortened. There are 41 rows in the real programming)

Example 1

D    E       F
           $125.00
2    X     $ 55.00
           $125.00
           $150.00
           $600.00
     X     $145.00
           $ 75.00
      
     F46 = $255.00

Example 2

D    E       F
           $125.00
2    X     $ 55.00
           $125.00
           $150.00
           $600.00
2    X     $145.00
           $ 75.00
      
     F46 = $400.00

Can this be accomplished? How?

Thanks for any insight into this issue.

Wayne

 

[BubbaBBQ]

If you did not have the quanity issue, I would have recommended the SUMIF() function. It would have looked something like this: =sumif(E2:E9, "X",F2"F9) assuming the data was in rows 2 through 9.

Since you want the user to enter the quantity, why not ask them to place an quantity in column E even if it's a 1. It's just as easy to place as an "X".

So I think I would put this formula in column G. =if(G2<>"",E2*F2, 0). I would title this column as "Extended Total". Or the formula could read: =if(G2 <> 0,E2*F2, 0).

On the row where you want the total place this formula: =sum(G2:G9)*1.07. This totals the Extended column and adds 7%.

By elimnating your orignial column E (with the X), then you have the same number of columns and a very simple formula system. Of course the formulas would need to be adjusted to match the right columns if you did this.

Bubba

 

[sunnyland]

Hello Wayne,

One way to go about it:

Let's say I have data in D1 to F9 as follow

Qty Ordered Price
125
X 55
X 125
150
600
2 X 145
75
Result 470

The formula in F9 will be as follow:

=SUMPRODUCT(--(E2:E8="X"),(D2:D8=0)+(D2:D8<>0)*D2:D8,F2:F8)

Check out that it returns the wanted result.

this formula check first for the X in column E
then the trick to get the qty or 1 if empty :

(D2:D8=0)+(D2:D8<>0)*D2:D8

**********
ie: qty =5
we know that true =1
(D2:D8=0)+(D2:D8<>0)*D2:D8
false or (true and 5) will return 5

ie:qty=0 or false
true or (false and 0) will return 1 or true

as or [+] only need one value to be true to return true
and and [*]
*************
It is confusing and I get confused as well sometimes with these boolean.

To not get an error you should use data validation so only number can be entered in column D and F. You could have data validation as well in E, using list and entering X and ticking [ignore blank].


I hope this is hopefull, I didn't include the 7% tax as I wasn't sure as you say when there is an X it means tax but your result doesn't seems to include tax

Example 2

D E F
$125.00
2 X $ 55.00
$125.00
$150.00
$600.00
2 X $145.00
$ 75.00

F46 = $400.00
I am not sure this is an invoice or a quote or something else.
So if you need more help and need to include the tax post another post.

Regards
Francoise

 

[spectacularstuff]

That's a very good idea.

I can make some hidden columns on the price sheet and use that.

I had hoped to incorporate that multiplication process into the current programming if it was possible without the use of another column.

If not, I will use the hidden column.

Thank You by the way. Sometimes you cannot see the forest through the trees. It didn't even occur to me to figure in like * 1.07. That works great.

I have tested the hidden column and it works for what I need it for. I am still going to look to see if I am able to incorporate the multiplication process into the 1 cell. I would rather have 1 cell to change programming on when needed vs. 46 cells and another hidden column.

The excel document currently has a total of 12 columns. 4 which are already hidden and 50 rows. There was no sense in confusing the issue by mentioning this earlier because whatever will work for 1 column will work for all of them. That would mean a total of 4 more hidden columns * 40 something rows.

I do thank you for your help bubba. This looks like a very viable option.

Thank You,

Wayne

--------------------------------------------
I am just reading about sunnyland's approach. I tried something like that earlier but did not have the comma in there. Instead I had an *. I will attempt that and get back to you sunnyland. Thanks.

Wayne

 

[spectacularstuff]

Francoise,

That's brilliant. I attempted that but had the configuration way wrong. I was trying to get that but could not. It works. I should be able to figure out the tax issue. The following should work

=IF(SUMPRODUCT(--($E$4:$E6="X")),SUMPRODUCT(--($E4:$E45="X"),($D4:$D45=0)+($D4:$D45<>0)*$D4:$D45,F4:F45)*1.07,SUMPRODUCT(--($E4:$E45="X"),($D4:$D45=0)+($D4:$D45<>0)*$D4:$D45,F4:F45))

statement and that will work.

I am more concerned with the 20% off all services through the discount coupons. I can figure that out however. That is just another sumif or sumproduct because that is also done by an X.

Just to give you a heads up on what this is.
This is a computer repair company that has technicians out in the field. The technicians take their laptops with them and quote prices right onsite for work to be accomplished. This is the price sheet that automatically figures out how much to charge the client for work to be accomplished. It figures in tax and even when people have one of the many 20% off all services discount coupons we have out there.

 

[spectacularstuff]

Francoise,

What do you mean data validation?

"To not get an error you should use data validation so only number can be entered in column D and F. You could have data validation as well in E, using list and entering X and ticking [ignore blank]."

How exactly do I do that?

Thanks for any insight.

 

[spectacularstuff]

Okay, I just looked up Data Validation. Good Idea. I will read about that tomorrow as it is aroun 1am and I have to head home.

Thank You for all of your help everyone.

 

[Aladin Akyurek]

...

=IF(SUMPRODUCT(--($E$4:$E6="X")),SUMPRODUCT(--($E4:$E45="X"),($D4:$D45=0)+($D4:$D45<>0)*$D4:$D45,F4:F45)*1.07,SUMPRODUCT(--($E4:$E45="X"),($D4:$D45=0)+($D4:$D45<>0)*$D4:$D45,F4:F45))
[/code]
statement and that will work.
...

Why do you need IF instead of:

=SUMPRODUCT(--($E4:$E45="X"),($D4:$D45=0)+($D4:$D45<>0)*$D4:$D45,F4:F45)*1.07

or shorter:

=SUMPRODUCT(--($E4:$E45="X"),($D4:$D45=0)+$D4:$D45,F4:F45)*1.07

 

[sunnyland]

Hello to all of you,

Sorry I didn't answer straight away, I had to pick up the kids from school.

Wayne, I don't think the :

=IF(SUMPRODUCT(--($E$4:$E6="X")),SUMPRODUCT(--($E4:$E45="X"),($D4:$D45=0)+($D4:$D45<>0)*$D4:$D45,F4:F45)*1.07,SUMPRODUCT(--($E4:$E45="X"),($D4:$D45=0)+($D4:$D45<>0)*$D4:$D45,F4:F45))

[it must be a typo from yours the $e$4:$e$6]

will work nevertheless

I try checking the results to come up with the right formula:

SUMPRODUCT(($D$4:$D$45=0)+($D$4:$D$45<>0)*$D$4:$D$45,($E$4:$E$45<>"X")+($E$4:$E$45="X")*1.07,$F$4:$F$45)

will do the work: ie

having
X 12
5 15
2 x 13

will result in 115.66 wich is the right answer

meaning (12*1.07)+(5*15)+(2*13*1.07)

By data validation, I mean if by mistake you enter a D instead of an X then the formula will calculate as with no tax, just because you made a typo typing D instead of X.

By data validation you can limit what people can type in a column. Meaning sometimes it simplifies formula. Ie

You can access data validation via the menu Data.... Validation...
There you can limit your entries to numeric, boolean,time...... you can even have a list so people can only type in certain value. With numeric you can set min and max....

Or your validation could be a formula ie: the age of a person couldn't be greater than the age of his father/or mother.

For the 20% may be post a few lines of sample with columns if you need extra help. I am not familiar with american tax [I suppose you are]and don't know if coupons will be applied to taxable product on non taxable one.

I tried Aladin Solutions but didn't come up with the same results.

 

[spectacularstuff]

In the example below it would charge tax to the whole job. That would work if we charged tax on everything. However being service provider we are only required to charge tax when we sell a product. If we sell a product then we have to charge tax on the whole job. If we do not sell a product then we don't charge tax.

So I test to see if those first 3 cells have an X in them. Since those X's are associated with the products that we sell, I look at them first to decide whether or not there is to be tax added onto the whole amount.

Wayne

...

=IF(SUMPRODUCT(--($E$4:$E6="X")),SUMPRODUCT(--($E4:$E45="X"),($D4:$D45=0)+($D4:$D45<>0)*$D4:$D45,F4:F45)*1.07,SUMPRODUCT(--($E4:$E45="X"),($D4:$D45=0)+($D4:$D45<>0)*$D4:$D45,F4:F45))
[/code]
statement and that will work.
...

Why do you need IF instead of:

=SUMPRODUCT(--($E4:$E45="X"),($D4:$D45=0)+($D4:$D45<>0)*$D4:$D45,F4:F45)*1.07

or shorter:

=SUMPRODUCT(--($E4:$E45="X"),($D4:$D45=0)+$D4:$D45,F4:F45)*1.07