countif, countifs multiple criteria with dates

[charliebuoy]

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?

 

[JoeMo]

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]

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

 

[Grasor]

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]

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.