Search code examples
excelfunctionsumifs

Error when using SUMIFS from different worksheet

I'm currently developing a workbook which is designed to store daily stock trading data. It has two worksheets, a main worksheet which lists each days stock trading activity, and a master sheet which is designed to show year to date, month to date and week to date trading revenue per client.

I've attached a screenshot of the master worksheet and main worksheet columns columns below:

master

main sheet

I'm trying to run a sumifs function on the master sheet which will draw data from the main data sheet. So for example if I want to know how much client A traded on a year to date basis for my master sheet I would select columns A (account name) and K (gross commission) from the data sheet as part of the sumifs function and include the client name as criteria. However, this formula continues to return a value of 0 on the master sheet which is incorrect. I've included the sumifs formula I'm using below, any help would be appreciated. 

=SUMIFS(Maindata!A:A,Maindata!K:K,Master!A21)

Cell A21 refers to the location of the client name within the first column of the master worksheet.

Solution

  • Switch place for column K and A:

    =SUMIFS(Maindata!K:K,Maindata!A:A,Master!A21)

    Syntax for SUMIFS:

    SUMIFS(sum_range, criteria_range1, criteria1, [criteria_range2, criteria2], ...)

    Where:

    sum_range: range to sum (values you want to sum)

    criteria_range1: first criteria range

    criteria1: criteria to look/search for in your criteria range