CountIf Formula Help (2003)

[cayang68]

I have been trying to work a formula to count cells based on certain
requirements. I am not sure if CountIf is the right formula to use....

I have named the following colums.

N:N = Department
U:U = Feature
Z:Z = Calculation

The formula I am trying to do is

If Feature = path repair and Department = Concrete then count Calculation if
it = Path Repair False.

All the formulas I have tried to date have given me an error or an incorrect
figure.

I hope someone can help.
Thanks

 

[Sheeloo]

Try this
=SUMPRODUCT(--(Department="Concrete"),--(Feature="Path Repair"),Calculation)

You need to define a range of rows for Department, Feature, Calculation like
A1:A100 (and NOT A:A as you seem to have done)...

Whole column reference might work in Excel 2007

 

[T. Valko]

If you're using Excel 2003 then you can't use entire columns as range
references with this function.

Use cells to hold your criteria:

A1 = path repair
B1 = Concrete
C1 = Path Repair False

=SUMPRODUCT(--(N1:N100=A1),--(U1:U100=B1),--(Z1:Z100=C1))

 

[cayang68]

Sorry, This does not work. The value that came back was incorrect.

I have now defined a range of rows as suggested

Thanks

 

[cayang68]

Thank You. works well

If you're using Excel 2003 then you can't use entire columns as range
references with this function.

Use cells to hold your criteria:

A1 = path repair
B1 = Concrete
C1 = Path Repair False

=SUMPRODUCT(--(N1:N100=A1),--(U1:U100=B1),--(Z1:Z100=C1))

 

[Sheeloo]

I thought you wanted to sum the third column based on matches on first two
cols...

Glad you got the solution through Biff...

 

[T. Valko]

You're welcome. Thanks for the feedback!