Excel 2019, for Mac - sumif w/ lookup calc in Criteria

315 Views 5 Replies 2 Participants Last post by  etaf
Within a single data table, I am using the following LOOKUP calculation to locate the last occurence of a "text string" in a range from Column M, then report a "date" from the associated cell in Column B. Column B is in date order, past to current, top to bottom. The result of this calculation is a separate reporting point in it's own cell. It works perfectly!

=LOOKUP(2,1/(\$M:\$M=\$M\$106),\$B:\$B)

Then I'm trying to use a SUMIF calculation to sum cells in Column K that have an associated date value in Column B "greater than" the result of the LOOKUP calculation above. This is also a separate reporting point in its own cell. This calculation results in an error:

=SUMIF(\$B\$8:\$B\$1000,>LOOKUP(2,1/(\$M:\$M=\$M\$106),\$B:\$B),\$K\$8:\$K\$1000)

If I manually type the date from the LOOKUP calculation, the SUMIF calculation works. See below. I do not want to do this because the LOOKUP value can change as the "text string" is found in new rows of data periodically added to the table.

=SUMIF(\$B\$8:\$B\$1000,">11/15/2021",\$K\$8:\$K\$1000)

In the failed calculation above, I have tried a myriad of different syntaxes, i.e. quotes, parentheses, etc. to no avail. I'm thinking this may have something to do with having an array in the SUMIF "Criteria", but I'm just a rookie at this and cannot find anything to confirm that or suggestions on another way to extract the SUMIF data I'm looking for.

Any ideas would be greatly appreciated!
Status
Not open for further replies.
1 - 6 of 6 Posts
=SUMIF(\$B\$8:\$B\$1000,">"&LOOKUP(2,1/(\$M:\$M=\$M\$106),\$B:\$B),\$K\$8:\$K\$1000)

What version of Excel are you using

Works no error - returns a zero in excel 365 version - but thats just with blanks as i dont know your data structure or content

maybe include a sample spreadsheet with the error

May need a date() datevalue() function - not sure
OMG . . . that worked perfectly! Can you explain why quotes around the ">" operator and not the whole calculation? Also the use of the ampersand (&)?
not exactly , that was how I was taught to do it, well not taught - but read/videos/online forums etc , with a lot of functions
the ampersand is a Joining - concatenation

I know it works sometimes with "" around the lot , and also with numbers
Seen it often used on forums with dates as well
so
=SUMIF(C3:C14,">1",D3: D14)
will work
=SUMIF(C3:C14,">1/2/22",D3: D14)
A date is just a number , if format to general - from 1/1/1900

But whenever i use numbers , dates , text, formulas etc , I have always used that syntax
the operator in "" with an & to join

I'm sure there is a full explanation on why some expressions work and some dont

but to be honest i would not know off the top of my head
See less See more
Good stuff. Thanks for your help . . . much appreciated!
you are welcome
1 - 6 of 6 Posts
Status
Not open for further replies.