+ Reply to Thread
Results 1 to 2 of 2

Problem with array in SUM(SUMIF formula

  1. #1
    Registered User
    Join Date
    05-10-2016
    Location
    Italy
    MS-Off Ver
    2013
    Posts
    3

    Problem with array in SUM(SUMIF formula

    Hi, I have some probblems to generate a working formula like sum(sumif....)

    I need to read all the resources codes of a dept form sheet department an put a formula in sheet Totals by type via VBA code
    This because resources in department may change (add or delete resources)

    If i have one resource only in a despt i create a sumif formula and work
    If there are more than one resource i need to put a formula like sum(sumifs...) and put array into the criteria

    I have created a static version and it work but when i try to automate the formula creation with VBA don't work!

    Can anyone help me?

    Thanks in advance

    P.S. Sorry for my very bad english
    Attached Files Attached Files

  2. #2
    Forum Expert dflak's Avatar
    Join Date
    11-24-2015
    Location
    North Carolina
    MS-Off Ver
    2013 / 2016 / 365
    Posts
    7,151

    Re: Problem with array in SUM(SUMIF formula

    You are almost there. I think this is the formula you need
    =SUMIFS('DATI ESPORTATI'!$BN:$BN,'DATI ESPORTATI'!$A:$A,'GRUPPI RISORSE'!$D3,'DATI ESPORTATI'!$BP:$BP,B$1,'DATI ESPORTATI'!$BO:$BO,"SER")

    Notice the placement of the dollar sign ($). These keep things from changing when you copy a formula from one cell to another.

    For example if you use $A$1 in a formula, no matter where that formula is used, the $A$1 will not change.

    Now in your original formula, you made reference to "ALTRO", "TYPE 1", etc. These are your column headers. So for the first formula we want it to look at cell B1, for the next formula cell C1, and so on. So we want the column to change, but not the row. So we can use B$1 in the formula.

    Likewise, you use GRUPPI RISORSE'!D3 in the formula. We want to keep the column as D always and let the row vary so we can use GRUPPI RISORSE'!$D3.

    Likewise, I locked down the ranges BN:BN, BP:BP and BO:BO so they don't change when the formula is copied from one column to another.

    You should be able to copy this formula to the rest of the cells that need them.

    Read up on absolute reference and relative reference for Excel.
    Attached Files Attached Files
    One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.

    A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.

+ 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. [SOLVED] Help with SUMIF or array formula, please!!!
    By alchavar in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 11-06-2015, 01:09 PM
  2. Sumif formula on an array of criteria?
    By Dial1 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 08-07-2013, 05:29 PM
  3. Array inside the SUMIF problem
    By AHB10 in forum Excel Formulas & Functions
    Replies: 6
    Last Post: 01-12-2013, 10:10 AM
  4. Problem SUMIF-ing on an array
    By ANS in forum Excel General
    Replies: 6
    Last Post: 10-04-2012, 03:46 AM
  5. SumIf or array formula
    By zachvu in forum Excel General
    Replies: 11
    Last Post: 06-22-2010, 04:45 PM
  6. Array Formula with Sumif and between dates
    By Neil Glazin in forum Excel General
    Replies: 13
    Last Post: 01-15-2010, 11:27 AM
  7. [SOLVED] Modify SumIF... Array Formula
    By carl in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 05-17-2005, 03:06 PM

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