Count based on corresponding column on another worksheet

Issue solved. Thanks.

--Original Post --

Hi. Been at this all day and just can’t figure this out so, I hope you guys can show me the way.

My Excel file has 3 worksheets, “audit”, “staff”, and results”. The “audit” worksheet has columns for Name (C1), Type (D1), Year (E1), and Taxes (F1). The “staff” worksheet has columns for Name (B1) and Region (C1). The Names in the “audit” and “staff” worksheet are identical. The “results” worksheet has columns named Region 01 (E6), Region 02 (F6), and Region 03 (G6), and cells E8, F8, and G8 will contain the formula in question for each region.

For each region I need the formula to display a count of the rows in the “audit” work sheet where Type = “Sale”, Year = “”, and Taxes = “”. The “audit” tab does not contain a column for Region, but does have a Name column. The “staff” worksheet has the distinct names and their corresponding regions. This is where I get hung-up. How do I match the names in the “staff” worksheet and their corresponding regions to the Names in the “audit” worksheet so I can do a count by Region with the criteria for Type, Year, and Taxes?

Thanks in advance for your assistance.

I have uploaded a sample Excel file to illustrate the problem.

Here is what you desired... ( I hope )

Formula:

Please Login or Register  to view this content.

check the attached file..

That was the solution..
But I request all experts to refer this case, if I wanna try something like this.
Its not working up ( May be I am missing something )
Actually I want to limit the no. of element in the Countif Criteria Argument by using Index Small If construction.. ( just to speed up the calculations..as more the countif criteria array elements, more slowy it will be.. )
I tried to use Offset but problem remains..
here it is..
=SUMPRODUCT(COUNTIF(audit!$C$2:$C$724,INDEX(staff!$B$2:$B$93,SMALL(IF(staff!$C$2:$C$93=results!E$6,ROW($2:$93)-1),
ROW(INDIRECT("1:"&COUNTIF(staff!$C$2:$C$93,results!E$6)))))))
Here is the attached file..

@Vikas Thanks for your private message.

If you want to induce an array of returns using INDEX then you to apply some extra coercion to the row_num (or column_num) parameter, e.g. (array-entered):

=SUM(COUNTIF(audit!$C$2:$C$724,INDEX(staff!$B$2:$B$93,N(IF(1,SMALL(IF(staff!$C$2:$C$93=results!E$6,ROW($2:$93)-1),ROW(INDIRECT("1:"&COUNTIF(staff!$C$2:$C$93,results!E$6)))))))))

See here for more:

http://excelxor.com/2014/08/23/index...ray-of-values/

Regards

Fantastic XOR LX..
You are Great..
what an article.. , simply extra ordinary..
Here is my way.. which I learnt by reading your articles..
=SUM(COUNTIF(audit!$C$2:$C$724,INDEX(staff!$B$2:$B$93,SMALL(IF(staff!$C$2:$C$93=results!E$6,ROW($2:$93)-1),
N(INDEX(ROW(INDIRECT("1:"&COUNTIF(staff!$C$2:$C$93,results!E$6))),))))))

Thanks again for sharing your knowledge..
I am highly obliged

Regards,

You're welcome. Glad to share - that's one of the points of a forum, no?

Much thanks to all for your time and help. Works flawlessly. Should have sought the answer in this forum first instead of spending hours trying to figure this out on my own, but it's like banging your head against a wall...it feels so good when you stop! Thanks again.

You are always welcome..