Count data within certain month in a different sheet

[Gunther09]

Hi,

I'm trying to make a few formula's to get an overview of my data sheet.

1) Count the amount of rows that contain the month June-2018 in column 'J3 and down' sheet2. Then I want to be able to drag this formula down to also count the following months.
2) Count how many times the word "JA" occurs in column 'D3 and down' sheet2 within the month June-2018 (dates are noted in column J3 and down). Then I also want to be able to drag this formula down to count the following months aswell.

Date format is: d-m-yyyy

Any help would be greatly appreciated!

[Special-K]

Date format d-m-yyyy is irrelevant. Format of the cell is only what it LOOKS like.
What matters is whether the VALUE of that cell is an actual date or text. Which you haven't said. I'm assuming it's an actual date otherwise this won't work.

1. Try (untested) =SUMPRODUCT(--(TEXT(Sheet2!J3:J1000,"mmmm-yyyy"=J3))
2. Try (untested) =SUMPRODUCT((TEXT(Sheet2!J3:J1000,"mmmm-yyyy"=J3)*(D3:D1000="JA"))

[Gunther09]

Please close

[Special-K]

My mistake, should be 3 brackets at the end

Try these

1. Try (untested) =SUMPRODUCT(--(TEXT(Sheet2!J3:J1000,"mmmm-yyyy"=J3)))
2. Try (untested) =SUMPRODUCT((TEXT(Sheet2!J3:J1000,"mmmm-yyyy"=J3)*(D3:D1000="JA")))

[Gunther09]

Unfortunately still the same message. I attached the file and removed all sensitive information, so you can see it for your self.

[Special-K]

1. In your original description you stated "Sheet2".
We can't read minds. You need to tell us what the sheet is called. You didn't. I assumed it was called Sheet2.
If your Sheet is called something else then you need to change the name of the sheet in the formula.

2. You also left out the = sign in the formula so Excel will think your formula is data. Any Excel user knows when they enter a formula they need to enter an = sign.

3. This sheet does not match your original description, Row 3 is now a column header. You implied it was data.
"Count the number of rows that contain the month June-2018 in column J3."
It now looks as though you are matching that against the month, ONLY THE MONTH. They wont match.

There's too much messing around between what you originally described and what you actually want.
I'm done with this. This is the second time this week I've had someone describe something that doesn't reflect their actual required output.
Don't bother replying, I've unsubscribed from this thread.

You're on your own.

[Gunther09]

1. With sheet2 i ment second sheet, my bad for the incorrect formulation.

2. I left out the = sign on purpose because it was impossible to place the formula because of the error. I should have mentioned that.

3. The second sheet is where the data is coming from, the 3rd row there is actual data. See column 'J' in the second sheet for all dates (including June).

I'm sorry for wasting your time, and thanks for putting in the time anyway.