Countif or SUMIF?

[Deb6508]

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.

Thanks in advance for any help you can give.

 

[Andrew Poulsom]

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]

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]

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.

Thanks in advance!!!

 

[Deb6508]

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.

Thanks in advance!!

 

[barry houdini]

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]

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

 

[colbymack]

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]

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]

Just for fun... try:

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