CountIf Formula Help (2003)

  • Thread starter Thread starter cayang68
  • Start date Start date
C

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
 
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
 
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))
 
Sorry, This does not work. The value that came back was incorrect.

I have now defined a range of rows as suggested

Thanks
 
Thank You. works well

T. Valko said:
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))
Click to expand...
 
I thought you wanted to sum the third column based on matches on first two
cols...

Glad you got the solution through Biff...
 
Back
Top