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.
 

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
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"))
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top