# countif, countifs multiple criteria with dates

#### charliebuoy

##### New Member
Hi,

i am loosing the will with this one, please can someone help.

I have a spreadsheet that looks at dates for actions that are due next month, i have a formula that counts the total number of actions due next month in total

=COUNTIF(Actions!O3:O442,P16)

P16 is =DATE(YEAR(P14),MONTH(P14)+1,DAY(1))

above works fine,

The next formula is where it falls down; i need to search for actions log to look for specific areas, i am trying to use Countifs. i have tried the below formulas but both return #value. I have even tried to put them as array formula.

=COUNTIFS(Actions!C3:C442,"House",Actions!O3:O456,"="&P16)
=COUNTIFS(Actions!O3:O458,P16,Actions!C3:Q442,"house")

can anyone see my error?

### Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

#### JoeMo

##### MrExcel MVP
Hi,

i am loosing the will with this one, please can someone help.

I have a spreadsheet that looks at dates for actions that are due next month, i have a formula that counts the total number of actions due next month in total

=COUNTIF(Actions!O3:O442,P16)

P16 is =DATE(YEAR(P14),MONTH(P14)+1,DAY(1))

above works fine,

The next formula is where it falls down; i need to search for actions log to look for specific areas, i am trying to use Countifs. i have tried the below formulas but both return #value. I have even tried to put them as array formula.

=COUNTIFS(Actions!C3:C442,"House",Actions!O3:O456,"="&P16)
=COUNTIFS(Actions!O3:O458,P16,Actions!C3:Q442,"house")

can anyone see my error?
The count range and the criteria range are expected to be the same size.

#### charliebuoy

##### New Member
wow - works - i was so close, but so very far away - thanks you have blind excel user see

#### Grasor

##### Board Regular
For simple stuff like this I generally cheat and just use a hidden column to hold the month/year data as a summation then compare that to a like value for the current date. It appears you are only interested in the Month/Year combination, not necessarily the day of the month so give this a whirl. This doesn't copy very well into excel so you'll have to write it in. What I would do in this example is just hide column C when done.

I probably won't win any awards with this one but I believe it will get you the results you are after.

 A B C D E 1 =Today() =Month(A1)+Year(A1) 2 Action Due How Many Due Next Month 3 House 7/5/2015 =Month(B3)+Year(B3) House =countifs(A3:A7,D3,C3:C7,C1+1) 4 Car 8/1/2015 =Month(B4)+Year(B4) Car =countifs(A3:A7,D4,C3:C7,C1+1) 5 House 7/25/2015 =Month(B5)+Year(B5) Haircut =countifs(A3:A7,D5,C3:C7,C1+1) 6 Gun Cleaning 8/20/2015 =Month(B6)+Year(B6) Gun Cleaning =countifs(A3:A7,D6,C3:C7,C1+1) 7 Shower 8/25/2015 =Month(B7)+Year(B7)

<tbody>
</tbody>

#### JoeMo

##### MrExcel MVP
wow - works - i was so close, but so very far away - thanks you have blind excel user see

You are welcome - thanks for the reply.

Replies
1
Views
247
Replies
4
Views
156
Replies
0
Views
251
Replies
2
Views
210
Replies
1
Views
539

1,195,628
Messages
6,010,770
Members
441,568
Latest member
abbyabby

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