Hi,
Please assist in building a formula to lookup values referencing unique code from a large name bearing that code and with a second reference to "rent"
please find attached spreadsheet
Hi,
Please assist in building a formula to lookup values referencing unique code from a large name bearing that code and with a second reference to "rent"
please find attached spreadsheet
You can use this formula in B3:
=SUMIFS($I$4:$I$14,$H$4:$H$14,"Rent",$G$4:$G$14,"*"&A3&"*")
then copy down.
Note that you have zero shown in cells B5 and B7 - you need to correct your entries in A5 and A7.
You will probably need to use semicolons ( ; ) instead of commas ( , ) in the formula, depending on your regional settings.
Hope this helps.
Pete
Thanks Pete. Anychance we can tweak the formula without correcting entry in A5 (ignore A7, that was a typo)
This is just a sample of a huge file, so will be hard for me to pick up and correct entries in column A
This change (shown in red) works for your sample data:
=SUMIFS($I$4:$I$14,$H$4:$H$14,"Rent",$G$4:$G$14,"*"&LEFT(A3,5)&"*")
by just using a maximum of the first 5 characters in column A.
Hope this helps.
Pete
Thanks heaps Pete
You're welcome - thanks for the rep.
Pete
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks