Excel 2007 : COUNTIF and IF

**Deap** · 05-05-2010, 04:44 PM

Hi Guys,

This is probably a simple one for some of you, I know what I'm trying to say but not sure of the exact due to numerous arguments format.

I have a list of guys on my team and several status codes for jobs they complete, the only format we can have for recording this data is that the enter their "Name" "Job Reference" and "Status Code" for each Job,

I know I can calculate my findings using a pivot table but I'd rather just copy the data to a separate sheet and count them dynamically.

I'd like to

COUNTIF ('Sheet 2'!A:A)="value of, current Sheet Cell A2" & ('Sheet 2'!C:C)="value of, Current Sheet Cell B1"

I know this is half function half text, sorry. it just goes to show how I don't understand the calculation.

**NBVC** · 05-05-2010, 04:53 PM

Maybe?

=Sumproduct(--('Sheet2'!$A$2:$A$100=A2),--('Sheet2'!$C$2:$C$100=B1))

note: cannot use whole column references with Sumproduct in pre-2007, so adjust ranges to suit.

and if you are in 2007, then use Countifs

=Countifs('Sheet2'!A:A,A2,'Sheet2'!C:C,B1)

**Deap** · 05-05-2010, 05:28 PM

That's absolutely brilliant,

I have the following which works

=COUNTIFS('Failure Log'!$A$1:$A$500,A3,'Failure Log'!$C$1:$C$500,X1,'Failure Log'!$B$1:$B$500,"MY")

A1:A500 Matches the name of the user, the value of A3
B1:B500 Matches the function they perform, either MY, NB or FB which I will set manually across the board.
C1:C500 Matches the day of the week, the value of X1

However if I increase the range past 500 i get a #VALUE error, I assume Excel cannot handle a range greater than 500 cells, as I am going to be using a previous version of Excel in the workplace. I can't use a full A:A range.

Any suggestions? As I will have a range greater than 500 per week.

**Deap** · 05-05-2010, 05:45 PM

Hi all,

I will need to sign off for the night as I am at work early, I will mark this as solved tomorrow.

The only way I can think of doing this is reducing the data to 3 or four separate groups, per shift or per function. although I would still be close to if not exceeding 500 records per group.

As an idea I will probably be using about 2500 per week.

Is there a way in which I can tell a function to go to a page with the name "X" where x is the value of a cell, I could split my data by days but would like this formula to say,

Sheet 1 is grouped by days,
with a heading at the top of each with the day names

A1 is "Monday"
F1 is "Tuesday"
L1 is "Wednesday"
etc

If i group my data by day and name those worksheets Monday Tuesday Wednesday etc

Can I say to the formula look at sheet name <A1's value> instead of 'Monday'

**daddylonglegs** · 05-05-2010, 06:03 PM

Originally Posted by Deap

However if I increase the range past 500 i get a #VALUE error, I assume Excel cannot handle a range greater than 500 cells, as I am going to be using a previous version of Excel in the workplace. I can't use a full A:A range.

COUNTIFS can cope with many more than 500 rows no problem, perhaps you didn't make all the ranges the same size, that would give you a #VALUE! error

However if you are going to use the formula in Excel 2003 or earlier you won't be able to use COUNTIFS function (it's not available before 2007) so you'll have to revert to SUMPRODUCT as NBVC suggested.

If you have a cell with "Monday" [without quotes] you can use INDIRECT to refer to the sheet, e.g.

=INDIRECT("'"&L1&"'!A2:A2500")

where L1 contains the sheet name