Excel 2007 : Sumifs with Vlookup Criteria

[Filibuster]

I am trying to create a sumif equation that uses a simple first criteria, and a second vlookup type of criteria. The vlookup criteria should return as TRUE if the criteria2 value from table 1 corresponds to searched value in table 2 returning the value of 1 - if that makes sense. Please see the attached spreadsheet.

The equation would look something like this (although this one does not work):
=sumifs(sum_range, criteria_range1, criteria1, criteria_range2, vlookup(criteria_range2, table_array, col_index)=1)

Thanks for your consideration!

[jeffreybrown]

Hello & Welcome to the Board,

How about in J7...

=SUMIFS($C$6:$C$11,$A$6:$A$11,$A$1,$B$6:$B$11,$H7)

[Filibuster]

Thanks Jeff. Let me give you more data. In reality, table 1 and table 2 exist on different worksheets. In both cases I cannot change the format (add equations, etc) as the data is pulled from an external data source. Table 1 has about 2000 entries, and table 2 has around 50 entries.

In the simple example I posted, your formula gets the desired result (20). However, I need to be able to handle a larger table 2. Does that make sense?

[jeffreybrown]

Out of Table 1 and Table 2, are they both volatile, that is, could it be Table 2 is always the same length only the 0 or 1 changes?

Is PGM ID the only item which will change?

You didn't actually say where you want the results displayed. Could you update your example to answer some of these questions?

[Filibuster]

The number of rows in table 1 is variable depending on the external data source. I am not too concerned about it for this example - once I know how to do what I am asking, I can tailor it for the variable number of rows. The column arrangement of table 1 is fixed. Table 2 is more or less static (although the actual table is around 50 entries). The exact assignment of 0/1 in the OK column would change depending on the external data source. In this example, the only entry a user would make is PGM ID. The formula you are trying to help me with could be placed in any cell.

[zbor]

try this, confirmed with ctrl+shift+enter:

=SUMPRODUCT(SUMIFS(C6:C11, A6:A11, A1, B6:B11, IF(H6:H10=1, G6:G10)))

[romperstomper]

Shouldn't that be:
=SUMPRODUCT(SUMIFS(C6:C11, A6:A11, A1, B6:B11, IF(I6:I10=1, H6:H10)))
based on the example?

[Marcol]

Maybe?

Please Login or Register  to view this content.

[zbor]

Shouldn't that be:
=SUMPRODUCT(SUMIFS(C6:C11, A6:A11, A1, B6:B11, IF(I6:I10=1, H6:H10)))
based on the example?

You are right.. I've deleted one column...

@Marcol: no need of +-- it's like ++.... Only one + will be enough

[Marcol]

@ zbor
You don't need any of the "+"'s, just thought it made it clearer

Please Login or Register  to view this content.

It's more limited than your solution, but it doesn't need to be array entered.

If the values in Table 2 can change, I'd go your way.

[zbor]

or just + :

=SUMPRODUCT(--(A6:A11=A1),C6:C11,(B6:B11="B")+(B6:B11="C")+(B6:B11="D"))

I'm not even sure that mine solution is simpliest... I feel there's better one...

[romperstomper]

If there is, I'm sure DDL will be along with it in due course.

[Filibuster]

Thanks all for the ideas. We still don't quite have the solution. The data in table 2 can change, so I need a more generic solution that actually determines whether the ACCTLOOKUP column corresponds to a 0 or 1 in the OK column. Also, performance is going to be an issue - my experience with SUMPRODUCT is that it really takes a lot of resources. I'll need a very efficient formula to keep things manageable.

[zbor]

Did you try mine solution at all? For more efficient workbook you need to re-arrange data. So in table 1 add one more column and make VLOOKUP to Table2. Then you can make more efficient formula without SUMPRODUCT.

[Filibuster]

hi zbor - yes, I tried your solution - its works great for the sample data. But, it looks like you are hard coding table 2 values into the formula. I need something more generic that can handle a table 2 with 50 entries where the 1/0's change from day to day.

I thought about a vlookup for table 1 - but the project does not allow me to edit table 1 - it is an output from another process that I cannot change or alter. A thought is to maybe create a scratch worksheet that has the vlookups - I give that some consideration.

[romperstomper]

Zbor's code works with whatever sizes your tables are. Your constraints rule out efficient solutions as far as I can see.