SUMIFS and LOOKUP function resulted to NULL value

Hi All!

So I was trying to compute for the sum of all values of the "Assignments" in Data 1 that are classified as "Z1" in Data 2. I used the Lookup function as one of the criteria enclosed in SUMIFS but the returned value after resulted to null.

Please help me figure out the better way or correct way to do it.

I wrote the formula like this:

=SUM(SUMIFS('Data 1'!$I:$I,'Data 1'!$G:$G,Report!$C$2,'Data 1'!$A:$A,">="&Report!D$4,'Data 1'!$A:$A,"<="&EOMONTH(Report!D$4,0),'Data 1'!$E:$E,{"ZP","KZ"},'Data 1'!$C:$C,{"21200101","21300502"},'Data 1'!$B:$B,LOOKUP(Report!$B5,'Data 2'!$A:$A,'Data 2'!$B:$B)))

Please see the attachment for the sample file:

SampleProblemSUMIFSandLOOKUPfile.xlsx

[EDITED] There was a precondition of not adding any helper column to either Data 1 or Data 2. Would you know if there is a way to capture the values without adding a helper column?

In Sheet Data 1, I would add a Helper Column (J) with:

Formula:

Please Login or Register  to view this content.

copied down.

Then this:

Formula:

Please Login or Register  to view this content.

Note that the G/L Account Numbers are NOT in double quotes.

And why have you got thousands of blank rows in this sample file?

You're welcome. Thanks for the rep.

Oops. Big omission.

The LOOKUP doesn't work. FULL STOP. In isolation, try: =LOOKUP(Report!$B10,'Data 2'!$A:$A,'Data 2'!$B:$B)

Pretty sure it will always return the very last entry in Data 2 column B. Column A would need to be sorted but, even then, it won't be giving you what you want.

You could try putting the helper column on a separate sheet. The problem there is keeping it in sync.

I'll have to think about it but don't hold your breath.

@Fluff: that's impressive BUT, if you change the date to, say, February or March, the value doesn't move. Am I missing something? For example, Data 1, Row 13, change the date to February.

Sorted. Nice work. As you say, slow, but does the job.

I also found a formula that doesn't require a helper.
Please try

Formula:

Please Login or Register  to view this content.

I have tested this formula and it seems to work well.
This formula is fast, because the number of operations needed is relatively limited and it doesn't use functions known to be slow.

EDIT: This workbook contains an error. The corrected workbook is in post #15

@Hans: your formula, corrected:

Formula:

Please Login or Register  to view this content.

There were a couple of errors ( Data 2'! and a semi-colon) and B5 not locked.

@TMS, I see you changed $B5 to B5, but $B5 is correct and B5 is not correct.
$B5 is the planning group and it is in 1 column.

Edit, What I'm writing here is wrong, It's the other way around.
Sorry.
Thanks for the corrections.
I will adjust my sheet.

The semicolumn was a translate-error. I will work more accurately next time.

Here is a corrected workbook and formula:

Formula:

Please Login or Register  to view this content.

@TMS, thx for adressing the errors.

Sry for the confusion!

This formula is fast, because the number of operations needed is relatively limited and it doesn't use functions known to be slow.

I look forward for your feedback.

Originally Posted by Fluff13

@HansDouwe
I would also suggest adding an IFNA otherwise the formula will only work if the exact range is used, which is not normally very practical.

Good tip. I agree.

Hello pals, thanks for a lot of help. I tried recreating the formula, in the same file and set of data, but it prompted #VALUE! on the cell. I selected the entire column for the ranges. Also, is there an effect if the Assignment Column in Data 1 is not in a uniform format? Other contents are in text form and others are in number form.

Please Login or Register  to view this content.

As well as the problem of the headers being introduced to the range, you should ( MUST ) avoid full range references in array functions. Every column contains over a million cells and you are matrix multiplying lots of them together. I'd be surprised if the calculation was not VERY slow.

@TMS I know.
In my solution I AVOID full range references.
I use only the reference of the table not the whole columns.
The calculation was VERY fast.

Values below the table are not involved in the calculation.

@Hans: yes, I know. The point was made to the OP who is breaking your formula.