Count - multiple criteria, multiple sheets, and date range.

**threecliffs** · 06-14-2011, 06:12 AM

Fabulous site - am a huge novice in Excel and am desperate for urgent help ,so hope someone can assist.

I'm struggling with creating a report in 2003, and have attached a simplified example.

My date ranges are on sheet DATA SHEET, along with data validation lists, and holidays to use alongside NETWORKDAYS.

Can anyone suggest something for the following two examples and I hope to be able to work the rest out from there:

1) Count number of occurances over sheets 'Type A' and 'Type B' where C:C>='DATA SHEET'!A3 and C:C<='DATA SHEET'!B3 and B:B='1'.

The below worked for me on the first week, but gave a minus number for the second week:

=(SUMPRODUCT(('Type A'!$C$2:$C$3000>='DATA SHEET'!$A3)*('Type A'!$C$2:$C$3000<='DATA SHEET'!$B3)*('Type A'!$B$2:$B$3000-1)))+(SUMPRODUCT(('Type B'!$C$2:$C$3000>='DATA SHEET'!$A3)*('Type B'!$C$2:$C$3000<='DATA SHEET'!$B3)*('Type B'!$B$2:$B$3000-1)))

2) Count number of occurances over sheets 'Type A' and 'Type B' where D:D>='DATA SHEET'!A3 and D:D<='DATA SHEET'!B3 and B:B='1' and E:E=<5

2010 is much easier to use - I've tried lots of different ways in 2003 now and keep getting negatives, errors, or multiplied rather than counted results.

Please help!

Many thanks,
A very, very noobie.

**threecliffs** · 06-14-2011, 11:45 AM

Equally, if this isn't possible, could someone let me know. Still struggling with it :o(

Many thanks.

**NBVC** · 06-14-2011, 11:55 AM

In the formula above you have condition:

('Type A'!$B$2:$B$3000-1) and ('Type B'!$B$2:$B$3000-1)

should it be: ('Type A'!$B$2:$B$3000=1) and ('Type B'!$B$2:$B$3000=1)

**threecliffs** · 06-14-2011, 12:06 PM

Thanks NBVC. -1 was a last ditch attempt. I've tried it and --1, =1, all with quotes and without. None have worked.

Been playing around with all sorts but I think I'm missing something fundamental.... but I've note the foggiest what.

Thanks though!

**NBVC** · 06-14-2011, 12:12 PM

Are you looking in the right column the Type A and Type B sheets for the Date Processed... i.e. should you be looking in column D instead of C?

=(SUMPRODUCT(('Type A'!$C$2:$C$3000>='DATA SHEET'!$A3)*('Type A'!$D$2:$D$3000<='DATA SHEET'!$B3)*('Type A'!$B$2:$B$3000=1)))+(SUMPRODUCT(('Type B'!$C$2:$C$3000>='DATA SHEET'!$A3)*('Type B'!$D$2:$D$3000<='DATA SHEET'!$B3)*('Type B'!$B$2:$B$3000=1)))

I get result of 2 with that formula..

**threecliffs** · 06-14-2011, 01:27 PM

Thanks NBVC. Not quite it though I'm afraid.

I'll need to do the same calculation separately for both 'Date received' and 'Date Processed'.

In the attached example the Priority 1 'Date Received' for Week 1 date range should be a result of 4 i.e. 2 x 'Type A' Priority 1, plus 2 x 'Type B' Priority 1 received in specified date range.

But for 'Date Processed' the result is 2 i.e. 1 x Type Priority 1 processed, plus 1 x 'Type B' Priority 1 for same period.

Thanks so much for the help - I really need to get this done asap, and it seems like it should be so simple that I'm losing the will!

**NBVC** · 06-14-2011, 01:36 PM

Today seems to be a day of going in circles...

To get those results your original setup, with the =1 instead of -1 works..

i.e.

=(SUMPRODUCT(('Type A'!$C$2:$C$3000>='DATA SHEET'!$A3)*('Type A'!$C$2:$C$3000<='DATA SHEET'!$B3)*('Type A'!$B$2:$B$3000=1)))+(SUMPRODUCT(('Type B'!C$2:C$3000>='DATA SHEET'!$A3)*('Type B'!$C$2:$C$3000<='DATA SHEET'!$B3)*('Type B'!$B$2:$B$3000=1)))

and change the C ranges to D ranges to get 2.

=(SUMPRODUCT(('Type A'!$D$2:$D$3000>='DATA SHEET'!$A3)*('Type A'!$D$2:$D$3000<='DATA SHEET'!$B3)*('Type A'!$B$2:$B$3000=1)))+(SUMPRODUCT(('Type B'!$D$2:$D$3000>='DATA SHEET'!$A3)*('Type B'!$D$2:$D$3000<='DATA SHEET'!$B3)*('Type B'!$B$2:$B$3000=1)))