Advanced Countif

[SFCWoods]

Good morning, nothing can ever be easy in the Army. ;-)
I have a workbook with multiple worksheets. The First sheet contains totals
etc.
I want to be able to count all occurences of data in different tabs and
return those values to specific locations on the first sheet.
Example.
I have SGT Jones, SGT Smith and SGT Adams. Each of these have specific
mission numbers that I need to track for each month.
I want to track,for example, how many times each of them acquires a bolt and
how many times each acquires a nut for each month.
So I have OCT, NOV, DEC sheets along with the MAIN sheet in the workbook.
On the main Sheet I have each of the Soldiers names in Column A and I want
to tally the number of bots and nuts for each month in the columns to the
right.
I have tried multiple times to do a countif, but I can't get it to work right.

I can provide a sample of the workook if necessary. Any help would be
greatly appreciated.

 

[Don Guillett]

I might put all on ONE sheet starting at row 5
row 5 date>name>item
row 6 1/1/2007 jones bolt
in row1 month number jones bolt
in row 2 =sumproduct((month(a6:a600-1)*(b6:b600=b1)*(c6:c600=c1))
or filter the list and use SUBTOTAL

 

[Arvi Laanemets]

Hi

Or even better, create a Pivod table based on this consolidated table.

 

[SFCWoods]

How about if I sent you a sample? I can't really 'change' the layout, it is
very involved and contains a lot of data. It is really alot of data and I am
trying to make it so that it will automatically do some tallying to avoid
errors.

 

[Don Guillett]

I'm an old ex USAF personnel officer. Send to my address below

 

[SFCWoods]

I sent it, should be there all set to confuse you! lol

 

[Don Guillett]

=IF(LEN($A4)<2,"",SUMPRODUCT(--('PRODUCTION BY ARCC
OCT07'!$B$2:$B$100=$A4),--('PRODUCTION BY ARCC
OCT07'!F$2:F$100="x"))+SUMPRODUCT(--('PRODUCTION BY ARCC
OCT07'!$K$2:$K$100=$A4),--('PRODUCTION BY ARCC OCT07'!O$2:O$100="x")))