How do I get a count from one Excel tab to another tab using Xlookup and either Sumif(s), Countif(s), SumProduct, ect

Question

I have the following tabs in my workbook. I need a formula that gives me a either a count or a value of True / False in Tab 1, column PI11 when I do a XLookup between Tab 1 and Tab 2 on Epic Number and from the return array, (Tab 2 / Program increment), gets a count that matches the value in cell Tab 1 / B1, which is the column header name PI11.

Or, I need any formula that will provide my expected results based upon this data setup.

My expected results in Tab 1 under the column header PI11 is SFEPIC0002056 = 2 and SFEPIC0003486 = 1

Any help will be greatly appreciated.

Bryan

TAB 1

A	B
Epic Number	PI11
SFEPIC0002056
SFEPIC0003486

TAB 2

A	B	C
Epic Number	Feature Number	Program increment
SFEPIC0002056	SFFEAT0017633	PI14
SFEPIC0002056	SFFEAT0016976	PI11
SFEPIC0002056	SFFEAT0014810	PI12
SFEPIC0002056	SFFEAT0013648	PI11
SFEPIC0003486	SFFEAT0026976	PI12
SFEPIC0003486	SFFEAT0024810	PI11
SFEPIC0003486	SFFEAT0023648	PI13

Answer 1

Hi,

In cell B2 of sheet1, enter this formula and drag down

=COUNTIFS($E$2:$E$8,A2,$G$2:$G$8,B$1)

Hope this helps.

Answer 2

Hi Ashish.

I have to keep the summary and detailed data in two separate tabs. I was able to modify your formula and it works great.

Thank you for your quick assistance. Have a very nice day!

Bryan

Answer 3

You are welcome. Have a great day.