Hey all,
I have a table that contains a list of reservations and a table that contains a list of "BOMS" (bill of materials). I would like to count the number of times a BOM shows up in the reservation table in a specific fiscal year.
An example of each table:
<tbody>
</tbody>
Based on a reporting date of FY2010.
<tbody>
</tbody>
The "Count" column is the one that would contain the equation that I am looking for. The equation I currently have is like this:
=COUNTIFS(AllReservationsFiscal[BOMID],[BOMID],(INDIRECT("AllReservationsFiscal[FY"&$B$1&"]")),(INDIRECT("AllReservationsFiscal[FY"&$B$1&"]"))>0)
This returns a value of 0. This - [FY"&$B$1&"] - is a string that refers to my reporting date. In my example above, B1 = 2010.
Please let me know if you need more information.
I have a table that contains a list of reservations and a table that contains a list of "BOMS" (bill of materials). I would like to count the number of times a BOM shows up in the reservation table in a specific fiscal year.
An example of each table:
BOMID | FY2010 | FY2011 |
1 | 1 | 0 |
1 | 1 | 0 |
20 | 0 | 1 |
25 | 1 | 0 |
<tbody>
</tbody>
Based on a reporting date of FY2010.
BOMID | BOM Description | Count |
1 | Item 1 | 2 |
20 | Item 2 | 0 |
25 | Item 3 | 1 |
<tbody>
</tbody>
The "Count" column is the one that would contain the equation that I am looking for. The equation I currently have is like this:
=COUNTIFS(AllReservationsFiscal[BOMID],[BOMID],(INDIRECT("AllReservationsFiscal[FY"&$B$1&"]")),(INDIRECT("AllReservationsFiscal[FY"&$B$1&"]"))>0)
This returns a value of 0. This - [FY"&$B$1&"] - is a string that refers to my reporting date. In my example above, B1 = 2010.
Please let me know if you need more information.