+ Reply to Thread
Results 1 to 10 of 10

Formula to work out how many products among various products!

  1. 05-02-2013, 01:51 PM #1
    MissConfussed
    MissConfussed is offline
    Registered User
    Join Date
    05-02-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Formula to work out how many products among various products!

    Hi all,

    Im new on this forum so I apologise if I dont explain my dilemma too well.

    I have an assignment and one of the criteria is this..

    Enter Formulae in cells H2:H5 that work out how many books, Dvd's, CD's and Computer games are in the spreadsheet.

    Another criteria is

    Repeat the above but work out the Total price of all the books, Dvd's, CD's and Computer games

    I havent used spreadsheets for a while and im completely stuck.

    Please help

    If you can explain it as simply as possible I would be very grateful

    Thanks
    Attached Images Attached Images
    Reply With QuoteRegister To Reply
  2. 05-02-2013, 01:56 PM #2
    AZ-XL
    AZ-XL is offline
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Formula to work out how many products among various products!

    h2 cell =COUNTIF(A2:$A$25,$G2)

    i2 cell =SUMIF(A2:$A$25,$G2,C2:$C$25)

    the drop down
    Appreciate the help? CLICK *
    Reply With QuoteRegister To Reply
  3. 05-02-2013, 01:58 PM #3
    MissConfussed
    MissConfussed is offline
    Registered User
    Join Date
    05-02-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Formula to work out how many products among various products!

    Hi, Thanks for the answer but this didn't work
    Reply With QuoteRegister To Reply
  4. 05-02-2013, 01:59 PM #4
    tigeravatar
    tigeravatar is offline
    Forum Expert tigeravatar's Avatar
    Join Date
    03-25-2011
    Location
    Colorado, USA
    MS-Off Ver
    Excel 2003 - 2013
    Posts
    5,361

    Re: Formula to work out how many products among various products!

    MissConfussed,

    Welcome to the forum!
    What you need are the ...if() function.
    To count how many [item], use the Countif() function
    To sum the price of [item], use the Sumif() function
    To average the price of [item], you'll need to do Sumif()/Countif(), that is the Sumif() function divided by the Countif() function.
    Hope that helps,
    ~tigeravatar

    Forum Rules: How to use code tags, mark a thread solved, and keep yourself out of trouble
    Reply With QuoteRegister To Reply
  5. 05-02-2013, 02:00 PM #5
    AZ-XL
    AZ-XL is offline
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Formula to work out how many products among various products!

    =COUNTIF($A$2:$A$25,$G2)

    =SUMIF($A$2:$A$25,$G2,C2:$C$25)

    Itry this,
    sorry for previous message, just now realized my error
    Reply With QuoteRegister To Reply
  6. 05-02-2013, 02:03 PM #6
    AZ-XL
    AZ-XL is offline
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Formula to work out how many products among various products!

    =SUMIF($A$2:$A$25,$G2,$C$2:$C$25)

    I am too impatient and hasty
    Reply With QuoteRegister To Reply
  7. 05-02-2013, 02:04 PM #7
    MissConfussed
    MissConfussed is offline
    Registered User
    Join Date
    05-02-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Formula to work out how many products among various products!

    I will try these now.. thanks for the help
    Reply With QuoteRegister To Reply
  8. 05-02-2013, 02:12 PM #8
    MissConfussed
    MissConfussed is offline
    Registered User
    Join Date
    05-02-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Formula to work out how many products among various products!

    Ive managed to figure out the count criteria with the following formulae

    =COUNTIF(B2:B25,"Book")

    Is this right?

    Im still stuck on the Total Formulae! Nothing seems to be working
    Reply With QuoteRegister To Reply
  9. 05-02-2013, 02:18 PM #9
    AZ-XL
    AZ-XL is offline
    Valued Forum Contributor AZ-XL's Avatar
    Join Date
    03-22-2013
    Location
    Azerbaijan, Baku
    MS-Off Ver
    Excel 2007
    Posts
    603

    Re: Formula to work out how many products among various products!

    upload the will, like that will be easier for you

    your formula above is correct, but instead of writing "Book" reference the cell that contains this word
    Reply With QuoteRegister To Reply
  10. 05-02-2013, 02:24 PM #10
    MissConfussed
    MissConfussed is offline
    Registered User
    Join Date
    05-02-2013
    Location
    United Kingdom
    MS-Off Ver
    Excel 2003
    Posts
    5

    Re: Formula to work out how many products among various products!

    Hahahah.. Thanks! Wow, Its only taken me 2 hours of staring at my pc to try and figure this out!
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next 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

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1