+ Reply to Thread
Results 1 to 3 of 3

Count of Items sold based on changing date range

  1. 02-15-2024, 08:00 PM #1
    khaymar
    khaymar is offline
    Registered User
    Join Date
    02-15-2024
    Location
    California, United States
    MS-Off Ver
    365 MSO
    Posts
    1

    Count of Items sold based on changing date range

    OK . I am probably overthinking this but here goes.

    ON the attached worksheet. Using MS 365 MSO.

    On the calculations tab i want to count how many of a specific item has been sold over a given date range that I export from a third party tool.

    I will keep adding to the list of sales and would rather not have to go into each formula and update the range whenever it changes.

    I am using Countif as that seemed easy enough but if there is a better way I am open to it.

    Basically calculate how may times an item in the range "'Sale Details'!$J$2:$J$26" was sold and bring that count into the calculations tab for the respective item.

    Does that make sense?

    I greatly appreciate any guidance you can give.
    Attached Files Attached Files
    Reply With QuoteRegister To Reply
  2. 02-15-2024, 08:36 PM #2
    6StringJazzer
    6StringJazzer is offline
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,721

    Re: Count of Items sold based on changing date range

    In your sample, you seem to want counts of all items in the entire data set in Sale Details rather than wanting counts only within a particular date range.

    If what you care about is counting all occurrences of each item, regardless of dates, then you are on the right track, but you can use the entire column.

    However, I am baffled as to why the list of product names in Calculations doesn't match the products names in Sale Details. Seems like it would be a lot easier to do this:

    Values as displayed
    D
    E
    2
    Product
    Total Sales since 9/5/23 (Countif)
    3
    VOYAGER 8200 UC, B8200, BLACK
    3
    4
    Jabra Evolve 20 Special Ed. Stereo MS
    6
    5
    Jabra Evolve 40 MS Stereo Headset On Ear
    8
    6
    Jabra Evolve 75e Microsoft
    3
    7
    Jabra Evolve 75 MS Stereo - headset
    1
    8
    Logitech M510 Wireless Mouse 910-001822
    1
    9
    Logitech M525 Wireless Mouse 910-002585
    0
    10
    Kingston DataTraveler SE9 USB flash drive - 64 GB - USB 2.0 metal casing
    1
    11
    Kingston 128GB USB FLASH DataTraveler Exodia
    1
    12
    USB TYPE C TO USBC CABLE 3.1 5A M/M 6FT
    1
    Calculations


    Underlying formulas
    D
    E
    2
    3
    =COUNTIF('Sale Details'!$J:$J,D3)
    4
    =COUNTIF('Sale Details'!$J:$J,D4)
    5
    =COUNTIF('Sale Details'!$J:$J,D5)
    6
    =COUNTIF('Sale Details'!$J:$J,D6)
    7
    =COUNTIF('Sale Details'!$J:$J,D7)
    8
    =COUNTIF('Sale Details'!$J:$J,D8)
    9
    =COUNTIF('Sale Details'!$J:$J,D9)
    10
    =COUNTIF('Sale Details'!$J:$J,D10)
    11
    =COUNTIF('Sale Details'!$J:$J,D11)
    12
    =COUNTIF('Sale Details'!$J:$J,D12)
    Jeff
    | | |會 |會 |會 |會 | |:| | |會 |會
    Read the rules
    Use code tags to [code]enclose your code![/code]
    Reply With QuoteRegister To Reply
  3. 02-15-2024, 09:19 PM #3
    HansDouwe
    HansDouwe is online now
    Forum Guru HansDouwe's Avatar
    Join Date
    06-21-2022
    Location
    Nederland
    MS-Off Ver
    365 V2403 (Build 17330.20000)
    Posts
    6,429

    Re: Count of Items sold based on changing date range

    It is also possible to create the whole table with a single cell formula (no copy needed):

    Please try in D15:
    Formula: copy to clipboard 
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by HansDouwe; 02-15-2024 at 09:21 PM.
    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)

Similar Threads

  1. Inventory Management - Invoice doesn't count correctly sold items
    By N7000 in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 04-13-2023, 07:19 AM
  2. [SOLVED] Tiered Credit Count for items sold
    By 10100m in forum Excel Formulas & Functions
    Replies: 13
    Last Post: 04-20-2020, 01:26 AM
  3. Calculate items sold within 10 days prior to specified date
    By joeltes in forum Excel General
    Replies: 1
    Last Post: 03-16-2018, 12:52 PM
  4. [SOLVED] Count the number of items sold in one column based on the quantity in another column
    By mike_m1 in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 08-20-2013, 05:48 AM
  5. We want a formula that will show items sold by the day and the net amount sold on that day
    By Mark Quan in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 07-02-2013, 09:17 PM
  6. Calculationg number of items sold after a specific date
    By JohnFletcher66 in forum Excel General
    Replies: 3
    Last Post: 10-28-2011, 07:39 AM

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