0

I have a data set which shows test results, each person my have several rows of data. My labels are

ID - Test Date - Test Result - Interval Between Tests

I want to sum the interval between tests for entries with the same ID, and return the test result when the interval sum is 365 days.

I have used =SUMIF(Sheet1!A:A,"="&VLOOKUP(Sheet2!A17, Sheet1!A2:K62,1,FALSE),Sheet1!K:K)

to sum all the interval days between tests for each individual but I want to know which test occur 365 days from the first test. Any ideas?

0

The Vlookup inside the Sumif does not make much sense, since you return the result from the 1st column of the lookup table. By definition that is the same as the lookup value. So, let's first re-write the formula to

=SUMIF(Sheet1!A:A,Sheet2!A17,Sheet1!K:K)

In words: Sum all values in column K where column A is equal to Sheet2!A17

Sumif() can only use one condition. If you want to add a condition that the interval column needs to be equal to 365, then you need Sumifs() instead. In this function, the sum range comes first, then the condition pairs, so, assuming that the interval is in column D on sheet 1, we need:

=Sumifs(Sheet1!K:K,Sheet1!A:A,Sheet2!A17,Sheet1!D:D,365)

In words: Sum all values in column K where column A is equal to Sheet2!A17 AND column D is equal to 365.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.