Formula that returns the count of items that meet two criteria

Autumninked

New Member
Joined
Jan 15, 2014
Messages
6
I'm hoping someone can help me because I've been searching the web and beating my brain over this for a day now.

I have a spreadsheet where I need a formula that will count how many sites have a document type "Lease Review" that are due between 9/1/14 and 9/30/14 only. The result that should come back is "2", but I can't seem to get any formulas I have found to work out correctly. A sample of what I'm working with is below. Any help would be greatly appreciated.

Site #Lease Document TypeReceived Forecasted
E243MOL2/3/2014
E245 MOL5/1/2014
E246Lease Review10/4/2014
E247Lease Reviewn/a
E251Lease Review11/15/2014
J059Lease Reviewn/a
J082Lease Reviewn/a
J083Lease Reviewn/a
M088Lease Review2/1/2014
M090MOL9/2/2014
P308Lease Reviewn/a
P325Lease Reviewn/a
P327Lease Reviewn/a
P414Lease Reviewn/a
P415Lease Reviewn/a
P442Lease Review5/1/2014
P480Landlord Notification/Consent6/30/2014
P517Lease Review6/30/2014
P526 Landlord Notification/Consent2/1/2014
S024Lease Reviewn/a
S028Lease Review8/12/2014
S029Lease Review10/1/2014
S030Lease Review9/6/2014
W031Lease Review7/26/2014
W033Lease Review9/6/2014

<COLGROUP><COL style="WIDTH: 52pt; mso-width-source: userset; mso-width-alt: 2523" width=69><COL style="WIDTH: 110pt; mso-width-source: userset; mso-width-alt: 5339; mso-outline-level: 1" width=146><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3145; mso-outline-level: 1" width=86><TBODY>
</TBODY>
 

Some videos you may like

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Jonmo1

MrExcel MVP
Joined
Oct 12, 2006
Messages
44,061
Welcome to the board..

Try

=SUMPRODUCT(--(B2:B100="Lease Review"),--(TEXT(C2:C100,"mmyy")="0914"))
 

Autumninked

New Member
Joined
Jan 15, 2014
Messages
6
I will give it a try and do a few test runs on it and let you know. I've never used TEXT before and haven't seen any formulas that included it, so I never even considered it. Thank you!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,564
Messages
5,596,863
Members
414,106
Latest member
Tigretto

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