# SUM or COUNT?

#### Raiiden

##### New Member
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.

Thanks

### Excel Facts

How can you turn a range sideways?
Copy the range. Select a blank cell. Right-click, Paste Special, then choose Transpose.

##### MrExcel MVP
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.

Thanks

Try...
Code:
``````=SUMPRODUCT(
--('2008 Schedule'!\$A5:\$A10000=36),
--('2008 Schedule'!\$M5:\$M10000=1))``````

#### Raiiden

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

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

##### MrExcel MVP
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

##### New Member
Hehe, I thought they were line separators

It works now, thank you very much!

#### Raiiden

##### New Member
It did work

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

Any thoughts?

#### Raiiden

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

No worries.

Replies
1
Views
250
Replies
4
Views
164
Replies
10
Views
1K
Replies
0
Views
192
Replies
5
Views
425

1,191,524
Messages
5,987,103
Members
440,079
Latest member
MarchePR

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back