+ Reply to Thread
Results 1 to 8 of 8

Money Calculation

  1. #1
    Registered User
    Join Date
    10-25-2005
    Posts
    23

    Money Calculation

    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

  2. #2
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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)

  3. #3
    Registered User
    Join Date
    10-25-2005
    Posts
    23
    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... ??
    Last edited by alen_re; 01-30-2006 at 08:41 PM.

  4. #4
    Registered User
    Join Date
    10-25-2005
    Posts
    23
    I still need this one badly

  5. #5
    Roger Govier
    Guest

    Re: Money Calculation

    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" <alen_re.22hldb_1138690203.7263@excelforum-nospam.com> wrote
    in message news:alen_re.22hldb_1138690203.7263@excelforum-nospam.com...
    >
    > 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
    >




  6. #6
    Registered User
    Join Date
    10-25-2005
    Posts
    23
    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 ?!

  7. #7
    Roger Govier
    Guest

    Re: Money Calculation

    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" <alen_re.22hrdy_1138698001.0303@excelforum-nospam.com> wrote
    in message news:alen_re.22hrdy_1138698001.0303@excelforum-nospam.com...
    >
    > 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
    >




  8. #8
    Forum Moderator daddylonglegs's Avatar
    Join Date
    01-14-2006
    Location
    England
    MS-Off Ver
    2016
    Posts
    14,676
    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1