Money Calculation

[alen_re]

Hello everyone...

I got this formula as a request for help...

=SUMPRODUCT((Selling!F5:F30000="Laser printers
mono")*(Selling!A5:A30000>=--F1)*(Selling!A5:A30000<=--F2)*Selling!H5:L30000)


Now when I can calculate number of printers sold.. I have also T
column.. in which I have information about the earned money on each
printer... so..
Now I need to calculate the money earned, information is in T column,
using the previous formula that calculates pieces sold between two
dates.. (F1 and F2).. I hope you get it.. Thanks for participating

 

[daddylonglegs]

I presume it would just be

=SUMPRODUCT((Selling!F5:F30000="Laser printer
mono")*(Selling!A5:A30000>=--F1)*(Selling!A5:A30000<=--F2)*Selling!H5:L30000*Selling!T5:T30000

 

[alen_re]

I tried this.. It doesnt work for me..

 

[alen_re]

Sorry.. this one didn't work out for me.

 

[alen_re]

I still need this one badly :

 

[Roger Govier]

Hi

The solution posted by daddylonglegs should work fine.
If you are getting a numeric result from
=SUMPRODUCT((Selling!F5:F30000="Laser printers
mono")*(Selling!A5:A30000>=--F1)*(Selling!A5:A30000<=--F2)*Selling!H5:L30000)
then adding the additional term to multiply by
Selling!T5:T30000
should turn this into a value, provided the data in T5:T30000 is
numeric.

Maybe you have some text values in column T rather than numbers. You
could try

=SUMPRODUCT((Selling!F5:F30000="Laser printers
mono")*(Selling!A5:A30000>=--F1)*(Selling!A5:A30000<=--F2)*Selling!H5:L30000*(--Selling!T5:T30000))

 

[alen_re]

Even with your great instructions I just cant get it to work...
I've formated the cells in T column as numbers, but it doesn't work...

The numbers in T column are also from a formula that calculates th
margin on some products.. and in cells in this column, if I don't hav
other values to calculate the margin.. I get #N/A.. usually I hav
problems with formulas if I have #N/A in some cells.. So i tried t
hide N/A under T columns with this formula
=IF(ISNA(YourFormula),"",YourFormula)
but when I do this.. I dont get a value in T cells... this is freakin
me out...

Would anyone help if I upload my file ?

 

[Roger Govier]

Hi

Don't post your file to the NG.
If you want to send me a copy of the file directly, I will take a look
at it for you.

Remove NOSPAM from my email address to send direct

 

[daddylonglegs]

Hi Alan,

I'd say you have a couple of options....

1 Get rid of #N/A in column T then the formula I posted should work
What formula are you using in T?

2 Switch to a CSE formula which can be constructed to ignore erro
values in column T, i.e.

=SUM(IF(ISNUMBER(Selling!T5:T30000),IF(Selling!F5:F30000="Lase
printer
mono",IF(Selling!A5:A30000>=--F1,IF(Selling!A5:A30000<=--F2,Selling!H5:L30000*Selling!T5:T30000)))))

This needs to be confirmed with CTRL+SHIFT+ENTER so that curly brace
appear around the formula in the formula ba