I have a spreadsheet of raw data that includes a cost based on a product code. I am trying to create a table that collates that information into an easy to read overview.
The cell I am currently working on needs to check the raw data to match the code to equal “Accommodation” and also check that Project Number matches then return the most frequent value (which in the raw data is always the same figure if those two criteria are met).
What I have currently is:
=INDEX(Dec_23[Costed For],MODE(IF((Dec_23[AHS Product Code]=”Accommodation”)*(Dec_23[Project No.]=[@[Project No.]]),MATCH(Dec_23[Costed For],Dec_23[Costed For],{0,0}))))
This formula works exactly as I need it to, however, if there are no results that match the exact criteria I get an #N/A result.
What I need is if there are no matches for the criteria and I need a value of 0 to be returned, this will allow my total column to work.
I have tried adding ISNA(…) in multiple different places in the formula but cannot get this function to work.
Quinten is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.