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 printers mono")*(Selling!A5:A30000>=--F1)*(Selling!A5:A30000<=--F2)*Selling!H5:L30000*Selling!T5:T30000)

[alen_re]

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

It was you who helped me, and gave me this formula.. before I had one formula that was just calculating how many cells are there between few dates, withou additional quantities..


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

Maybe with this one we can put T column here.. and somehow it would calculate money earned... ??

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

--
Regards

Roger Govier


"alen_re" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

[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 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 ?!

[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

--
Regards

Roger Govier


"alen_re" <[email protected]> wrote
in message news:[email protected]...
>
> 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
>

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