Formula to Match 2 Conditions [Days360}
Hi All
I need help with a Formula that would match the contents of a cell in column D with cell content in Column C. with a 2nd condition been the name of the tool. These condition being satisfied, the formula will perform a Days360 on the adjacent cells.
I have attached an example spreadsheet [the data I intend on importing will have 100's of rows, need to see if this will work first.] and highlighted in yellow the type of match up. Thanking you in advance.
Wally
| Attachment | Size |
|---|---|
| Previous Codes.xlsx | 9.65 KB |
Some solution
Hi,
For your example you can use the following array formula:
= DAYS360(INDIRECT("$A" & MATCH(1, ($C$1:$C$101 = $D9) * ($B$1:$B$101 = $B9), 0)), $A9)
Note: 1) After you enter the formula press CTRL + SHIFT + ENTER instead of only ENTER.
2) The above formula finds the first matching of the conditions and ignore any further if any.
3) The formula returns #N/A error if there is no match.
Just curious: What exactly you try to achieve, because, as Nick's suggestion, there can be a better solution of your requirements?
P.S. Check your data because, for example, in cell A4 is entered 1st December 3010 and this produced some undesirable results in calculation.
Best regards.
Solution
Hi
Many Thanks! Your formula does the job superbly.
I don't think that's actually
I don't think that's actually what you want...
Correct me if wrong, but you're trying to work out the amount of days XXX has the spanners before he returns them , and then charge him for the days used..
Change your data format to this:
Then you can create a pivot table and pivot on Who borrowed, and Sum the Days borrowed.
Input
Thanks for your Input Nick, much appreciated.