Countif or SUMIF?

Deb6508

Board Regular
Joined
Oct 25, 2004
Messages
131
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.
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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
Joined
Jul 14, 2005
Messages
333
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
Joined
Oct 25, 2004
Messages
131
Hmmm I input as previously instructed but get a count of 1 on every day even days without requests. :cry:

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

Thanks in advance!!!
 

Deb6508

Board Regular
Joined
Oct 25, 2004
Messages
131

ADVERTISEMENT

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

MrExcel MVP
Joined
Mar 23, 2005
Messages
20,825
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
Joined
Oct 25, 2004
Messages
131

ADVERTISEMENT

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

colbymack

Active Member
Joined
Jul 14, 2005
Messages
333
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
Joined
Oct 25, 2004
Messages
131
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
Joined
Aug 31, 2005
Messages
5,828
Just for fun... try:

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

Watch MrExcel Video

Forum statistics

Threads
1,119,139
Messages
5,576,306
Members
412,716
Latest member
thviid
Top