Count the number of matching text strings between 2 tables with multiple criteria

[Sketchin]

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:

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]BOMID[/TD]
[TD]FY2010[/TD]
[TD]FY2011[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]0[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]1[/TD]
[TD]0[/TD]
[/TR]
</tbody>[/TABLE]

Based on a reporting date of FY2010.

[TABLE="class: grid, width: 100"]
<tbody>[TR]
[TD]BOMID[/TD]
[TD]BOM Description [/TD]
[TD]Count[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]Item 1[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]20[/TD]
[TD]Item 2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]25[/TD]
[TD]Item 3 [/TD]
[TD]1[/TD]
[/TR]
</tbody>[/TABLE]

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.

 

[DILIPandey]

Hi Sketchin,

with these two tables in sheet 1 and sheet 2, use below formula in C2 of sheet 2:-

=SUMPRODUCT((Sheet1!$A$2:$A$5=Sheet2!$A2)*(Sheet1!$B$1:$C$1=Sheet2!C$1)*(Sheet1!$B$2:$C$5))

and drag down the formula.

C1 contains the heading as FY2010


Regards,
DILIPandey

 

[Sketchin]

Hi Sketchin,

with these two tables in sheet 1 and sheet 2, use below formula in C2 of sheet 2:-

=SUMPRODUCT((Sheet1!$A$2:$A$5=Sheet2!$A2)*(Sheet1!$B$1:$C$1=Sheet2!C$1)*(Sheet1!$B$2:$C$5))

and drag down the formula.

C1 contains the heading as FY2010


Regards,
DILIPandey

I think Im close. This is what I now have:

=SUMPRODUCT((AllReservationsFiscal[BOMID]=$GG14)*((INDIRECT("AllReservationsFiscal[FY"&$B$1&"]"))=$C$1)*(INDIRECT("AllReservationsFiscal[FY"&$B$1&"]")))

This returns 0 in a column called FY2016. I think the problem is in this equation:

(INDIRECT("AllReservationsFiscal[FY"&$B$1&"]"))=$C$1).

If I highlight it and press F9, it returns an array of "False"'s.

 

[Sketchin]

So I changed the equation to this:

=SUMPRODUCT((AllReservationsFiscal[BOMID]=$GG14)*(AllReservationsFiscal[[#Headers],[FY2009]:[FY2031]]=$C$1)*(INDIRECT("AllReservationsFiscal[FY"&$B$1&"]")))

and now I get the total times that each BOMID shows up in the first table in ANY fiscal year. If I evaluate this:

AllReservationsFiscal[[#Headers],[FY2009]:[FY2031]], I get {False,False,False,False,False,False,False,TRUE,False,False,False,False,False, etc.......}

 

[DILIPandey]

Hi Sketchin,

can you show up the sample data where this formula is getting failed ?


Regards,
DILIPandey