SUM or COUNT?

[Raiiden]

I am trying to add up the number of rows on sheet one that meet two different criteria, and produce the result in sheet 2.

1) Column A contains "36" - this ranges from 1 to 52

2) Colum M contains "1" - this is always 1 or 0

My code:

=COUNT(AND(COUNTIF('2008 Schedule'!$A5:$A10000,"36"),COUNTIF('2008 Schedule'!$M5:$M10000,1)))

It just seems to return "1" all the time wheteher anything is added or not.

Any ideas please?

Thanks

 

[Aladin Akyurek]

I am trying to add up the number of rows on sheet one that meet two different criteria, and produce the result in sheet 2.

1) Column A contains "36" - this ranges from 1 to 52

2) Colum M contains "1" - this is always 1 or 0

My code:

=COUNT(AND(COUNTIF('2008 Schedule'!$A5:$A10000,"36"),COUNTIF('2008 Schedule'!$M5:$M10000,1)))

It just seems to return "1" all the time wheteher anything is added or not.

Any ideas please?

Thanks

Try...

=SUMPRODUCT(
    --('2008 Schedule'!$A5:$A10000=36),
    --('2008 Schedule'!$M5:$M10000=1))

 

[Raiiden]

I tried that thanks but I just get a 0 with that code.

=SUMPRODUCT(('2008 Schedule'!$A5:$A10000=36),('2008 Schedule'!$M5:$M10000=1))

 

[Aladin Akyurek]

I tried that thanks but I just get a 0 with that code.

=SUMPRODUCT(('2008 Schedule'!$A5:$A10000=36),('2008 Schedule'!$M5:$M10000=1))

Why do you remove the -- bit which precede each term?

 

[Raiiden]

Hehe, I thought they were line separators

It works now, thank you very much!

 

[Raiiden]

It did work

If I add new data to the first sheet the cell now becomes "#N/A"

Any thoughts?

 

[Raiiden]

I found out my own answer, it was becuase the lookup coloumn contained #N/A.

No worries.