Share via

Create array to sum considering column & row headers

Anshul Marele 0 Reputation points
2024-02-20T19:03:10.2+00:00

Create array to sum considering column & row headers Sheet

Microsoft 365 and Office | Excel | For business | Windows

2 answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Ashish Mathur 101.9K Reputation points Volunteer Moderator
    2025-08-01T23:40:43.6+00:00

    Hi,

    In cell B5, enter this formula

    =SUMIFS($G$5:$G$10,$F$5:$F$10,$A5:$A8,$E$5:$E$10,B$4:C$4)

    Hope this helps.

    User's image

    Was this answer helpful?

    0 comments
  2. Michael Taylor 61,221 Reputation points
    2024-02-21T15:45:11.4933333+00:00

    I'm still not clear what you're trying to accomplish but given your expected results it looks like you want to pivot your lookup data such that each job type is a column that sums the invoices by date. In that case you want to use a pivot. In Excel you have to create a pivot table.

    1. Insert a PivotTable
    2. Select your dataset rows from the headers to the end of the table.
    3. In the wizard set the columns to use for the pivot.
      1. Drag and drop the Job Date column to the Rows field since each day is a separate row.
      2. Drag and drop the Job Type column to the Columns field since you want to pivot and create a column for each type.
      3. Drag and drop the #Invoices column to the Values field since this is what you want to aggregate.

    Excel generates a pivot table containing the data that I believe you want. You can customize it from there if desired.

    enter image description here

    Was this answer helpful?

    0 comments

Your answer

Answers can be marked as 'Accepted' by the question author and 'Recommended' by moderators, which helps users know the answer solved the author's problem.