Counting the occurrence of 2 Criteria on a row in a workbook

I am having trouble trying to count the occurrence of 2 Criteria on a row across a workbook to appear on a separate worksheet

I have used COUNTA, COUNTIF, SUMIF, SUMPRODUCT...and have goy stuck-to be honest I am out of my league.

To experiment with formula's I have built a small 'scaled down' spreadsheet with 4 people and 3 'mistakes' for 2 months. In the past this approach has always helped and I have been able to adapt my results to a larger scale. This is attached to the post.

The criteria that I am trying to count are a persons 'name' in Column A and a 'mistake' in Column B on the 'Month's' tabs

I am looking for a formula that will count how many time's Joe has made each mistake each month on the 'Data' tab

The actual formula would be used in a spreadsheet with over 100 users name and a dozen mistakes (these are in named ranges) and have multiple tabs (1 for each month. Each of the months tabs are in the same format with lots of other data) The named ranges would also be amended/updated overtime (high turnover of staff etc).

I am beginning to think what am trying to do is not possible without having to use any VB. I don't have never built anything in VB and am trying my utmost to avoid this option.

If anyone needs anymore information please give a shout and if anyone can help, that would be greatly appreciated.

Chris

How about the elusive COUNTIFS? Plural that is.

Try =COUNTIFS('Month 1'!$A$2:$A$5,Data!$A6,'Month 1'!$B$2:$B$5,Data!C$2) in B3 of the data tab. Copy throughout

Hi

Thanks, that is further than I got and counts the date from the 1st month tab 'Month 1'. What about month 2? Could I just use * and the same string but add and etc for each month? For example...

=COUNTIFS('Month 1'!$A$2:$A$5,Data!$A6,'Month 1'!$B$2:$B$5,Data!C$2)*('Month 2'!$A$2:$A$5,Data!$A6,'Month 2'!$B$2:$B$5,Data!C$2)

Hmm...yeah well you would use + not * for countifs. But there is a scalability issue with that. Jeffreybrowns' solution may be better. Or it might be easier for you to keep all the mistakes listed on one tab

Originally Posted by jeffreybrown

Always nice to say what does not work or even who you are referring too.

Your profile states 2003, is this accurate?

I realised as I was typing that i was using * and should have used +. It still did not work and came up with a #VALUE! but I am going to try the tally option now anyway as a solution unless anything else comes up.

At the moment I am using Excel 2008 on my mac at home.