Excel - Is there a better solution than SumIf in SumProduct for Multiple columns of data

0
votes

I have a large set of data I'm trying to get monthly totals out of as easy as possible.

| Start Date | Activity 1 | Activity 2 | Activity 3 |...| Activity 10 |

Each row can be chosen from a range of pre-made activities. Each activity has cost, labor hours, and a frequency. I have all that formula figured out but can only get it to work with a single column at a time.

The main part of the formula looks like so and this outputs a sum of costs for the entire column of the table based on some conditions:

=SUMPRODUCT(SUMIFS(Activities.Material Cost,Activities.Type,Facility.Table[Activity 1],Activities.Frequency,"Daily (5 day)"),--(Facility.Table[PM Start]<Month))

I then repeat this for different time intervals (daily, weekly, monthly, quarterly, semiannually, annually) because each has a different multiplier or conditional for monthly cost.

But I can't figure out how to repeat all these formulas across 10 data columns instead of 1 without a lot of copying or a macro, which is my last resort.

Any ideas? I tried putting a multi-column array in for the main array but it just errored out.

1
excelexcel-formula
Put sample data here to your post. I assume there may some other methods to achieve your output. So, input and expected output will help us to understand better. - Harun24hr

1 Answers

0
votes

Solved my problem:

=SUMPRODUCT(SUMIFS(Activities.Material Cost,Activities.Type,Facility.Table[[Activity 1]:[Activity 10]],Activities.Frequency,"Daily (5 day)")*--(Facility.Table[PM Start]<Month))

When using multiple columns in SUMPRODUCT you need to multiply with your conditional segments instead of separating them with columns.