Forum Discussion
Excel Formula Help
Woops. I thought I'd fixed that. These are tricky, and you recognized the error that slipped by me.
See if this is doing it now.
So far as multiple purchases from A17, so long as each appears on its own line, this will work. And it sure looks as if each order, each purchase, is on a row by itself. Said another way, as long as there aren't two module codes on the same row for any one purchase, this function will work as written.
After converting your tables on the results sheets to plain ranges, one formula in cell A8 on each sheet:
=FILTER(CHOOSECOLS(FormsData,XMATCH(A1,FormsData[#Headers]),14,15,16,17,18),(FormsData[Job Code]=C1)*(FormsData[District]=A1),"None found")
one formula in cell B3 of each results sheet:
=SUM(CHOOSECOLS(A8#,5))
Sheet FP XYZ123 will come up with an error since there's no District FP in the DATA sheet (nor a header FP in columns I:M in the DATA sheet). You can always wrap the whole formulae in IFERROR.
Thank you for your help! this just showed up today so i will give it a try as well.
