Urgent Excel 2003 question

[marieemery]

Please can you help with creating a formula for the following?

My colleage has sent me a file with COUNTIFS however this function doesn't appear to work in excel 2003 and I understand that I may need to use SUMPRODUCT?

I have 2 spreadsheets in a workbook that list employee names, employee numbers:
1. Spreadsheet has employee name, number, absence code and absence in hours/days (only for employees with absence in the last month)
2. Spreadsheet has all employee details including name, number etc

I am trying to create a formula that will pull the absence data from sheet and input this into sheet 2. It would need to pick up both the employee number, absence code

Hope this makes sense

 

[xenou]

Hi,
Can you post the CountIfs Formula? Or is it just a bunch of #REF errors now ...

 

[marieemery]

Thanks for the quick reply, it is

=COUNTIFS(ALVXXL01!A:A,C4,ALVXXL01!C:C,"0200")

 

[marieemery]

Perhpas I need to use a VLOOKUP?

The absence data is on one sheet and the full employee details is on the other - I need to pull through for those employees who have had sick leave the amount of instances of absence codes

So something like... if this employee number = this employee number and has instances of 0200 absence, count these and return the value here in (days / hours)

 

[xenou]

Okay,
try this:
SUMPRODUCT(--(ALVXXL01!$A:$A=$C$4),--(ALVXXL01!$C:$C="0200"))

I actually don't know if sumproduct works on whole columns ... we may be about to find out ....


----------------
Though countif/countifs won't be in days, hours ... just "number of instances" ...

 

[marieemery]

Thanks, returns #NUM!

Am I perhaps trying to do too much in 1 cell / formula?

 

[xenou]

Okay,
so try:
=SUMPRODUCT(--(ALVXXL01!$A$1:$A$1000=$C$4),--(ALVXXL01!$C$1:$C$1000="0200"))

Maybe it's the whole column thing ....
If that doesn't work we'll ask what's in C4 and some sample data from A1:A10 and C1:C10

 

[xenou]

A good point from shg in a similar thread just recently:

Are the values you're searching for numeric, and some of the values in the columns numbers stored as text (which will be immediately apparent if you lave Excel's default alignment alone)?

COUNTIF will ignore the distinction, SUMPRODUCT won't.

So our use of sumproduct could give different results, possibly ...


shg's post mentioned:
(http://www.mrexcel.com/forum/showthread.php?t=416975&page=2)