# SUMPRODUCT for information of MONTH and TEXT (2 criteria)

1. ## SUMPRODUCT for information of MONTH and TEXT (2 criteria)

Hi all,

I am currently working on a turnover report and i am having some problems using the countif formula to count e.g. the number of staff resigning on a certain month with a certain title.
Capture.JPG

I tried to search online and found that I will have to use SUMPRODUCT instead of COUNTIF for this matter as there are the month column and text column. However, I have not too familiar with this function.
Can anyone help so that i can have the result of:
in January, there are 1 Sales Associate leaver and 1 Senior Sales leaver and 1 Sales Associate leaver in Apr

Note that I only look at the month instead of the date.

2. ## Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

Formula:
`Please Login or Register  to view this content.`

3. ## Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

Here's a sample sheet. You could also do this by referencing cells. The date you see in cell E1 does not have to be January 1, it only has to be a date in January.

4. ## Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

Thank you for your quick response. I have tried using your formula in the sample sheet and i think i understand how it works but when i try to use it in my report, no matter what date or position i type in, the result is 0.

5. ## Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

The data sample can be relatively small.

If your report is sensitive make a copy delete all but a few rows and make up dummy info for the sensitive stuff. Leave the formulas as you tried them so I can see what is going on.

ViewPic

7. ## Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

it works fine now.
I found out what's going wrong when i was trying to create the dummy file.
I didnt realize the formula is date sensitive and simply used 31 as the last date of all months.
Thank you for your help. i thought i couldnt use countif for this case. thanks heaps!!

8. ## Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

I didnt realize the formula is date sensitive and simply used 31 as the last date of all months.
My cell reference formula is not date sensitive and uses the end of month function to ensure it has the correct beginning and ending dates no matter what month or year it is, including leap years.

With the hard coded date formula it is up to the user to enter the correct beginning and ending dates for the month and year in question.

See post #6, by not acknowledging it, you may end up on some ignore lists, including mine.

9. ## Re: SUMPRODUCT for information of MONTH and TEXT (2 criteria)

thanks for the information. i have read the page in post #6. new to these forums didn't know much about how it actually works.

There are currently 1 users browsing this thread. (0 members and 1 guests)