+ Reply to Thread
Results 1 to 6 of 6

Array Formula Help

  1. #1
    Registered User
    Join Date
    05-10-2021
    Location
    Miami
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Array Formula Help

    So I'm preparing a Sales Budget Template.

    I need an array formula to Total (sum all the QTY by the price of Each product for ALL Stores for every month) (Need Formula for GREEN CELL) on sample sheet.

    Note: Not all prices for the same item are the same.

    There are more than 100 stores with the same 5 items. Attached for reference I only put 5 Stores.

    Thank you!

    M.A.
    Attached Files Attached Files

  2. #2
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Array Formula Help

    this should work
    =SUMIFS(E$11:E$34,$B$11:$B$34,$B2,$C$11:$C$34,$C2)
    Since the store does not matter , so you are summing based on Product & Item
    If you needed to use the store number that would be more complex as the store ID is in a merged cell
    Attached Files Attached Files
    Last edited by etaf; 08-13-2021 at 05:55 PM.
    Wayne
    if my assistance has helped, and only if you wish to , there is an "* Add Reputation" on the left hand side - you can add to my reputation here

    If you have a solution to your thread - Please mark your thread solved do the following: >
    Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

  3. #3
    Registered User
    Join Date
    05-10-2021
    Location
    Miami
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: Array Formula Help

    Thank you etaf, but in the total "GREEN" Cell I need the "Sales Amount". So I need to Multiply Each Item X times price and sales qty for each store.

  4. #4
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Array Formula Help

    sorry , i missed that
    does this work
    =SUMPRODUCT(($D$11:$D$34*E$11:E$34)*($B$11:$B$34=$B2)*($C$11:$C$34=$C2))
    you may need array entry
    control+shift+enter

    is your office version 10 = MS Office 2010 ?
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    05-10-2021
    Location
    Miami
    MS-Off Ver
    Microsoft 365
    Posts
    6

    Re: Array Formula Help

    Works like Magic! Thank you etaf.!

  6. #6
    Forum Expert etaf's Avatar
    Join Date
    10-22-2004
    Location
    Wittering, West Sussex, UK
    MS-Off Ver
    365 (Mac OSX) (16.83 (24031120))
    Posts
    8,686

    Re: Array Formula Help

    you are welcome

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Replies: 10
    Last Post: 10-26-2020, 12:18 PM
  2. Array summing inventory of a specific part (array formula) EXCEL 2007
    By fdirosa in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-07-2018, 12:51 PM
  3. Replies: 3
    Last Post: 04-02-2016, 08:16 PM
  4. Can I make the row lookup array/range part in an array formula variable?
    By OLLY-7 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 07-18-2014, 09:06 AM
  5. Array formula + Array formula with criteria that lookups a Table
    By anrichards22 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 09-16-2013, 11:41 AM
  6. Replace hard coded array in formula with link to input array
    By David Brown in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 01-08-2011, 07:45 AM
  7. [SOLVED] Tricky array formula issue - Using array formula on one cell, then autofilling down a range
    By aspenbordr in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-27-2005, 11:05 AM

Tags for this Thread

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