0

The array formula I have currently is very close to what I want, it is only limited by the fact that I can only have two conditions (unless I want to manually add more). What I'd like to do is be able to use a name derived from a table column to match criteria, similar to using OR() with a range.

As it stands, my array formula looks similar to this (using names or range references) 

=SUM((JOB_HRS)*(TEXT($E4,"0")=TEXT(EMP_ID,"0"))*(JOB_DATE>=$M4)*(JOB_DATE<=$N4)*((JOB_TYPE=CONFIG!$F$8)+(JOB_TYPE=CONFIG!$F$9)))

I get a report sheet with about 5000 rows of data. This formula accurately parses the sheet and sums up the total hours worked by an employee (by ID which is sometimes stored as a number or text based on the output programs' feelings that day)

The last part is what I'm looking to streamline

((JOB_TYPE=CONFIG!$F$8)+(JOB_TYPE=CONFIG!$F$9))

It works, but I am limited a bit by the amount of cells, plus the formula looks way long. I'd like to simplify it to something like this, but cannot find a good reference to work with the SUM array. Any thoughts?

(JOB_TYPE=RNG_OF_JOB_TYPES)

Edit: Added picture

Expected Results

Improve this question
17
  • Any reason why you don't simply use a PivotTable? Commented Nov 29, 2017 at 19:53
  • From what I have found PivotTables use a hard coded reference for filters and I need to be able to update mine dynamically with cell references. Commented Nov 29, 2017 at 20:03
  • Use a Slicer to filter the Pivot. End result is the same, implementation and User Experience much simpler. Commented Nov 29, 2017 at 20:08
  • I suppose part of my reservation is formatting. I have the sheet formatted perfectly to print on one sheet. I don't want a PivotTable to grow and shrink. Plus I am trying to super restrict what the end-user can adjust on the sheet itself using protected cells and it is very heavy with conditional formatting. I could do this with VBA super easy, but I am even limited with the security policies on the computers this will be used on. Commented Nov 29, 2017 at 20:26
  • Won't the PivotTable show the exact same amount of records as your formula? Also, will this formula be executed for each and every Emp_ID? How many of those do you have? Have you looked at the SUMIFS function? Note that SUMIF/SUMIFS are computationally expensive, and should be used sparingly. The last thing you want is a worksheet with tens of thousands of SUMIFS in it. Commented Nov 29, 2017 at 21:12

1 Answer 1

Reset to default
1

Your best bet formula-wise is to use the SUMIFS formula. You will still need to specify each criteria individually, but the syntax is much simpler:

=SUMIFS(sum_range,criteria_range,criteria,...)

But as discussed, my personal preference is to use PivotTables to do number crunching. Sometimes I put the Pivot on a hidden sheet, and use the GETPIVOTDATA function in the sheet exposed to the user to get inputs and go retrieve the aggregated value from the PivotTable. This gives you complete flexibility over formatting.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.