SUMIFS of a list from a list

[hpatel517]

Hello all,

I'm having a hard time figuring out if this needs to incorporate a match function in there.

Essentially, what I'm trying to do is consolidate a formula that's in Column E all into Column D. Currently column E does it manually but I need to incorporate the lookup functionality into column D so it's all done there without having the luxury of changing what's in the Data tab or adding more columns to the Analysis tab.

Each Breakdown # is a group of jobs, and I want Column D to pull the sum of hours from the Data tab for that group of jobs at each line. I then want to divide this by the total hours in that whole initiative. In SQL teams, this would be a GROUP BY..

THANK YOU!!

[AliGW]

How about this?

=SUMIFS(Data!B:B,Data!A:A,">="&ROUNDDOWN(C2,-2),Data!A:A,"<"&ROUNDUP(C2,-2))/SUM(Data!B:B)

[hpatel517]

Thank your response. This does not work because in the larger data set, the job codes are not nearly as clean as in the example, and we have some that are 100s that belong in the 1.2 category and vice versa. The only distinguishable way to group the job codes is by the breakdown #.

I hope this helps!

[hpatel517]

I've updated the sample a bit to help illustrate what I want to accomplish which is essentially combining the formula for Columns D and E into just one in column D.

[63falcondude]

Based on the sample from post #4, try this:

=SUMPRODUCT((B$2:B$8=B2)*(C$2:C$8=Data!A$2:A$8)*(Data!B$2:B$8))

[hpatel517]

Based on the sample from post #4, try this:

=SUMPRODUCT((B$2:B$8=B2)*(C$2:C$8=Data!A$2:A$8)*(Data!B$2:B$8))

Thank you. So this works if the sorting of the data does not change, but as and when a piece is added in the same format, the look up does not work because I think it's using the row numbers as an index.

Column D is showing this formula, and Column E shows what the value should be.

[AliGW]

So how many more changes to the sample data will be required before we get something truly representative to work with?

Make sure, when you provide sample data, that it takes everything into account.

[avk]

As per your required data, i don't understanding of your logic.
As i understanding, as per you job, the total should be in job group i.e. 100+ but <=200, 200+ but <=300, .... so on.

[hpatel517]

Not necissarily, the job # is just a randomly assigned number. The only grouping is being done by the key in column B

[avk]

But no co relation each other. How to find out?

[hpatel517]

No correlation. They are assinged as new jobs open up

[FlameRetired]

Edited

... This does not work because in the larger data set, ...

So how many more changes to the sample data will be required before we get something truly representative to work with?

Make sure, when you provide sample data, that it takes everything into account.

Something you haven't mentioned and no one has asked: How many rows of source data will there be and at most how many rows of output do you anticipate?
Reason being the following is an array formula. If you are not aware of it array formulas are resource hungry. If there are too many or the arrays too large the workbook will take a performance hit. End edit

Try array entering this in E2 and fill down. If you aren’t familiar with array-entered formulas array enter means the formula must be committed from edit mode by simultaneously pressing and holding down Ctrl and Shift while hitting Enter.

Please Login or Register  to view this content.

[FlameRetired]

Here's another. Still an array formula, but does not need Ctrl + Shift + Enter.

Please Login or Register  to view this content.