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


--
alen_re
------------------------------------------------------------------------
alen_re's Profile: http://www.excelforum.com/member.php...o&userid=28331
View this thread: http://www.excelforum.com/showthread...hreadid=506593

I presume it would just be

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


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=506593

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


--
alen_re
------------------------------------------------------------------------
alen_re's Profile: http://www.excelforum.com/member.php...o&userid=28331
View this thread: http://www.excelforum.com/showthread...hreadid=506593

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


--
alen_re
------------------------------------------------------------------------
alen_re's Profile: http://www.excelforum.com/member.php...o&userid=28331
View this thread: http://www.excelforum.com/showthread...hreadid=506593

I still need this one badly :(


--
alen_re
------------------------------------------------------------------------
alen_re's Profile: http://www.excelforum.com/member.php...o&userid=28331
View this thread: http://www.excelforum.com/showthread...hreadid=506593

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))

--
Regards

Roger Govier


"alen_re" wrote
in message ...

I still need this one badly :(


--
alen_re
------------------------------------------------------------------------
alen_re's Profile:
http://www.excelforum.com/member.php...o&userid=28331
View this thread:
http://www.excelforum.com/showthread...hreadid=506593

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 the
margin on some products.. and in cells in this column, if I don't have
other values to calculate the margin.. I get #N/A.. usually I have
problems with formulas if I have #N/A in some cells.. So i tried to
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 freaking
me out...

Would anyone help if I upload my file ?!


--
alen_re
------------------------------------------------------------------------
alen_re's Profile: http://www.excelforum.com/member.php...o&userid=28331
View this thread: http://www.excelforum.com/showthread...hreadid=506593

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

--
Regards

Roger Govier


"alen_re" wrote
in message ...

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 the
margin on some products.. and in cells in this column, if I don't have
other values to calculate the margin.. I get #N/A.. usually I have
problems with formulas if I have #N/A in some cells.. So i tried to
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 freaking
me out...

Would anyone help if I upload my file ?!


--
alen_re
------------------------------------------------------------------------
alen_re's Profile:
http://www.excelforum.com/member.php...o&userid=28331
View this thread:
http://www.excelforum.com/showthread...hreadid=506593

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 error
values in column T, i.e.

=SUM(IF(ISNUMBER(Selling!T5:T30000),IF(Selling!F5: F30000="Laser
printers
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 braces
appear around the formula in the formula bar


--
daddylonglegs
------------------------------------------------------------------------
daddylonglegs's Profile: http://www.excelforum.com/member.php...o&userid=30486
View this thread: http://www.excelforum.com/showthread...hreadid=506593