Use a cell reference as a criteria in sumif and countif formulas

hannahstayo

I have a set of data, as shown below, and I am using formulas to pull data from the columns nights, ADR and total. I am using COUNTIFS and SUMIS formulas to collect the data, which are working well. The issue I have is that I need to replicate this data and tables for multiple different buildings and units. Right now I am going through and editing each formula to reference the specific apartment I want as a criteria, which is taking me a long time. So I wanted to see if there was anyway I could reference a cell and use that as a criteria instead?

Here is a selection of the master data.

Here is a screenshot of the "pivot tables" I have created for myself to pull the data I need from the master data. Right now the formula I am using for 'Count of name' is =COUNTIFS({1. LOS, ADR and Occupancy data Range 6}, "PP01", {1. LOS, ADR and Occupancy data Range 7}, "1") which references the master data. But I am having to update it for each row to be PP02, PP03 and such. Is there a way that I can change the formula so that it references the cell in the 'Unit' column, so it automatically searches for whatever is in that cell?

Nick Korna

Hi @hannahstayo,

If you change the "PP01" in your formula to [Unit]@row, then it will reference the contents of the Unit cell on the row.

You can then use this as a column formula, though you may wish to enfold it in an IFERROR so you don't get errors on the month row at the top, for example.

=IFERROR(COUNTIFS({1. LOS, ADR and Occupancy data Range 6}, [Unit]@row, {1. LOS, ADR and Occupancy data Range 7}, "1"),"")

Hope this helps, but if you've any problems/questions then just post! 🙂

Nick Korna

Try it without the IFERROR portion and see what the result is:

=SUMIFS({1. LOS, ADR and Occupancy data Range 10}, {1. LOS, ADR and Occupancy data Range 6}, [Unit]@row, {1. LOS, ADR and Occupancy data Range 7}, "1")

If it's still unparseable the only thing I can see that will have change is the additional data range so double check that.

Nick Korna

Hi @hannahstayo,

If you change the "PP01" in your formula to [Unit]@row, then it will reference the contents of the Unit cell on the row.

You can then use this as a column formula, though you may wish to enfold it in an IFERROR so you don't get errors on the month row at the top, for example.

=IFERROR(COUNTIFS({1. LOS, ADR and Occupancy data Range 6}, [Unit]@row, {1. LOS, ADR and Occupancy data Range 7}, "1"),"")

Hope this helps, but if you've any problems/questions then just post! 🙂

hannahstayo

Hi @Nick Korna thank you for getting back to me so quickly! That worked, thank you so much. You've saved me many many hours updating each individual formula for each apartment.

hannahstayo

Sorry @Nick Korna one last question. I also have a sumif formula

=SUMIFS({1. LOS, ADR and Occupancy data Range 10}, {1. LOS, ADR and Occupancy data Range 6}, "PP01", {1. LOS, ADR and Occupancy data Range 7}, "1")

If I wanted to do the same with this formula would I use the formula below or would it need to be changed slightly? I am trying it right now and its saying unparseable

=IFERROR(SUMIFS({1. LOS, ADR and Occupancy data Range 10}, {1. LOS, ADR and Occupancy data Range 6}, [Unit]@row, {1. LOS, ADR and Occupancy data Range 7}, "1"),"")

Nick Korna

Try it without the IFERROR portion and see what the result is:

=SUMIFS({1. LOS, ADR and Occupancy data Range 10}, {1. LOS, ADR and Occupancy data Range 6}, [Unit]@row, {1. LOS, ADR and Occupancy data Range 7}, "1")

If it's still unparseable the only thing I can see that will have change is the additional data range so double check that.

hannahstayo

Hey @Nick Korna that worked! Thanks so much for your help on this!

Nick Korna

No problem, glad everything is sorted now! 😊