# Countif or SUMIF?

#### Deb6508

##### Board Regular
Hi All,
I am trying to get a worksheet to compute the following criteria from another worksheet and am lost.

From the "Input from DOLR" worksheet.
Lookup the "Startdate" (column D)
Lookup the "LeaveCriteria" (column C)

From the "Calculations" worksheet.
If the Startdate from column D = 1/1/2006 and the corresponding column C = "R" then the date is counted. If both criteria are not met then that input is not counted.
There will be one cell for each date.
Hopefully this explanation makes sense.

### Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

#### Andrew Poulsom

##### MrExcel MVP
Try eg:

=SUMPRODUCT(--('Input from DOLR'!D1:D100=DATE(2006,1,1)),--('Input from DOLR'!C1:C100="R"))

adjusting the sizes of the ranges on 'Input from DOLR' to suit. Note that they must have the same number of rows and can't be entire columns.

#### colbymack

##### Active Member
You can replace the DATE(2006,1,1) with a cell reference if the dates jump around.

Something like this, where C2 is the Date to be evaluated from Calculations worksheet:
=SUMPRODUCT(--('Input from DOLR'!D1:D100=C2),--('Input from DOLR'!C1:C100="R"))

#### Deb6508

##### Board Regular
Hmmm I input as previously instructed but get a count of 1 on every day even days without requests.

Does anyone have any suggestions? I am getting as this is for vacation requests for several hundred people.

#### Deb6508

##### Board Regular

This is a sample from the DOLR worksheet

Column C Column D

LeaveCriteria StartDate
r 1 /2 /2006
R 1 /2 /2006
r 1 /2 /2006
R 1 /2 /2006
OUT OF RANGE PP1 ONLY 1 /2 /2006
OUT OF RANGE PP1 ONLY 1 /2 /2006

This is a sample from the Calculations worksheet.

Column A Column B Column C
1/1/2006 1/2/2006
Number of Requests 0 0
Number of Cancels

The formula that I have doing the calculations is:
=SUMPRODUCT(--('Input from DOLR'!\$D\$2:\$D\$2000=B1),--('Input from DOLR'!\$C\$2:\$C\$2000="R"))

I hope this clarifies things. Initially when I tried Andrew's solution I received 1's in every cell as whether there was an "R" or not.

#### barry houdini

##### MrExcel MVP
Are you trying to count upper case "R" but not lower case "r"?

The formula you quote is not case sensitive, perhaps

=SUMPRODUCT(--('Input from DOLR'!\$D\$2:\$D\$2000=B1),--(EXACT('Input from DOLR'!\$C\$2:\$C\$2000,"R")))

#### Deb6508

##### Board Regular

Hi Barry,
As long as there is an "R" upper or lower case doesn't matter it needs to be counted.

#### colbymack

##### Active Member
Deb6508,
Is your formula now doing what you want, or are you still having issues?

Using your sample data and the formula you last indicated
=SUMPRODUCT(--('Input from DOLR'!\$D\$2:\$D\$2000=B1),--('Input from DOLR'!\$C\$2:\$C\$2000="R"))

I get 4 as a result, which seems correct based on the criteria that you gave. 4 Rows have an R and the other 2 have the phrase "OUT OF RANGE PP1 ONLY".

The only differences I see between your formula and mine are the use of the fixed cell references (\$) and the reference to B1 as your starting date cell with D2 as your starting "range to be counted" cell and extending to D2000 (same for C of course).

Andrew's solution required that you change the date in each formula - hence the reference to 2006,1,1. This represents Jan 1, 2006. If you had one of those with an R corresponding, you would have gotten the same result as long as you did not change the 2006,1,1. That is why I put in the reference to the date cell (which you have incorporated).

Let us know if you are still looking for further guidance.

#### Deb6508

##### Board Regular
Thanks for the response colbymack.
I am still having problems with the formula. arghh I have double checked the arrays and cell references and am still receivinga 0 for each date. :x I have even went in and ensured that the date cells are formatted the same way thinking possibly that may be the problem.
Any additional thoughts would be greatly appreciated.

#### NBVC

##### Well-known Member
Just for fun... try:

=SUMPRODUCT(--('Input from DOLR'!\$D\$2:\$D\$2000=B1),--(TRIM('Input from DOLR'!\$C\$2:\$C\$2000)="R"))

Replies
7
Views
266
Replies
11
Views
228
Replies
7
Views
349
Replies
0
Views
107
Replies
7
Views
74

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,624
Messages
5,832,743
Members
430,160
Latest member
a_majda

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