Countif Month and another value match

[ooberandy]

I'm trying to build a formula which will count the amount of entries that match 2 criteria, the first that matches a month and the second which states either "Late" or "OK".

I get an #NAME? message when I use this formula =SUMPRODUCT(($C$2:$C$200=Late)*($D$2:$D$200=(MONTH($D$2:$D$200)=11)))

Can anyone help please

[ChemistB]

Without seeing your data. Late needs to be in quotes "late"

[ooberandy]

Thanks ChemistB

Capture.jpg

That's a screenshot of my data. I want it count how many entries match late or ok per month. I've tried your suggestion about the quote marks, but it just returns 0, when it should be 3

[ChemistB]

I can't see screenshots for some reason. You'd need to upload an example workbook (Go Advanced>Manage Attachments)

[ooberandy]

DRAFT.xlsx

Ok, thanks. I've attached it now...

[Vikas_Gautam]

Try this.. will definitely do the job..

Please Login or Register  to view this content.

[ooberandy]

Thanks Vikas, that works for the Late entry.

I've tried it for the OK entry, but it doesn't work correct. I've just swapped "Late" for "OK", would that be the correct way to do it?

[skywriter]

Thanks Vikas, that works for the Late entry.

I've tried it for the OK entry, but it doesn't work correct. I've just swapped "Late" for "OK", would that be the correct way to do it?

Hi I'm late to this party, but I'll answer that question for you.

The answer is yes, but if it's not working then I'll point a couple of things out.
The quotes " " in the formula are not optional, they must be there, if you changed the formula part from "Late" to OK without the quotes it won't work.

The same goes for the "*11*" if you changed that to a different number, say 12 it has to look like this. "*12*". Another issue would be that either the OK in your table or the OK you typed in the formula are different. If you accidently have a space in either your table or in your formula you will have a problem. This happens sometime and it's hard to catch. I would suggest copying and pasting one of the OK from you table and paste it into the formula just to make sure Excel is looking at the same thing.

The last thing I noticed was that in your attachment the date format of the dates in the D column are dd/mm/yyyy and those that are in the G column show as for example Jan-15 the underlying number was entered as 1/1/2015 which could be a d/m/y format but those for other months like March are 3/1/2015 which leads me to believe they are in a US format. So the reason I'm bringing this up is if you are trying to incorporate those cells into your formula to look at the C column you are going to have an issue.

Good Luck.

[Vikas_Gautam]

Use following to be more precise

Please Login or Register  to view this content.

Yes Using OK and 11 Combination will return 0 because there is no such combination in your data.
Read your data carefully you will get to know what I mean. However, formula is correct for what you meant.

[Indi_Ra]

Use this formula for "Late"
=SUMPRODUCT(($C$2:$C$37="Late")*((MONTH($D$2:$D$37)=MONTH(G2))*(YEAR($D$2:$D$37)=YEAR(G2))))
and this for "OK"
=SUMPRODUCT(($C$2:$C$37="OK")*((MONTH($D$2:$D$37)=MONTH(G2))*(YEAR($D$2:$D$37)=YEAR(G2))))

[skywriter]

Use this formula for "Late"
=SUMPRODUCT(($C$2:$C$37="Late")*((MONTH($D$2:$D$37)=MONTH(G2))*(YEAR($D$2:$D$37)=YEAR(G2))))
and this for "OK"
=SUMPRODUCT(($C$2:$C$37="OK")*((MONTH($D$2:$D$37)=MONTH(G2))*(YEAR($D$2:$D$37)=YEAR(G2))))

See my post #8 above regarding the dates in column D versus the dates in column G.

When you open the file look at the underlying entries for column G versus those for column D, are they different when you look at them.

On my computer they are entered two different ways and your formula doesn't work because of that?

What about you, can you just plug the formula in and have it work on the posted file?

[ChemistB]

Any formula where you treat the date as a number will not work because ooberandy's dates are entered as text.

Vikas's second solution is best as the first one could give faulty values on the 11th of any month.

If you want to stick with SUMPRODUCT (you shouldn't, Vikas's solution is more efficient)

=SUMPRODUCT(($C$2:$C$200="Late")*(ISNUMBER(SEARCH("/11/",$D$2:$D$200))))
=SUMPRODUCT(($C$2:$C$200="OK")*(ISNUMBER(SEARCH("/11/",$D$2:$D$200))))
Hope that helps.

[Indi_Ra]

See my post #8 above regarding the dates in column D versus the dates in column G.

When you open the file look at the underlying entries for column G versus those for column D, are they different when you look at them.

On my computer they are entered two different ways and your formula doesn't work because of that?

What about you, can you just plug the formula in and have it work on the posted file?

In column D and G are real data (numbers) but formatted differently. Therefore, we extracted the month and year (different days but no matter) in columns D and G and count how many "Late" or "OK" correspond to these months and years.
The formulas we checked in Attachment OP and it works.

In column G are also different years and I used it formula "drag" down, it works.

[newdoverman]

Here is something (untested) that could work for you

Please Login or Register  to view this content.

[skywriter]

In column D and G are real data (numbers) but formatted differently. Therefore, we extracted the month and year (different days but no matter) in columns D and G and count how many "Late" or "OK" correspond to these months and years.
The formulas we checked in Attachment OP and it works.

In column G are also different years and I used it formula "drag" down, it works.

That's interesting because when I open the file from post #5 and I type in a blank cell =ISNUMBER(D11) I get FALSE which seems to support what Chemist is saying in Post #12 that these are not numbers. When I type = ISNUMBER(G7) I get TRUE. If I try the month or year function on cell D11, I get #VALUE!. I'm not sure how it's working for you.

[newdoverman]

Column D contains values that are not numbers. However the correct day, month or year is extracted when using DAY, MONTH or YEAR functions. If the month is being correctly identified in column D and in column G then it doesn't matter whether they are both numbers or not for the calculation. =MONTH(D2)=MONTH(G2) returns TRUE.


The formula that I gave in $14 does appear to work when column D has the same number of values as the rest of the table.

[skywriter]

Column D contains values that are not numbers. However the correct day, month or year is extracted when using DAY, MONTH or YEAR functions. If the month is being correctly identified in column D and in column G then it doesn't matter whether they are both numbers or not for the calculation. =MONTH(D2)=MONTH(G2) returns TRUE.


The formula that I gave in $14 does appear to work when column D has the same number of values as the rest of the table.

Not on my computer, I get #VALUE!

=MONTH(D2)=MONTH(G2) returns TRUE

When I type
=MONTH(D10)=MONTH(D11)
Which are the same exact entries 16/01/2015 I get value again.

When I type
=YEAR(G6)=YEAR(G5) I get TRUE.

[newdoverman]

This may be of interest. I don't get the #Value error.

[skywriter]

This may be of interest. I don't get the #Value error.

Not working for me and I'm wondering why? The resultant values of your formula looked good until I changed the cell reference to a different cell and then I got #VALUE, changing back to the original reference doesn't fix anything.

Okay on a hunch I figured out what the issue is my default date format is US mm/dd/yyyy.
I changed it to dd/mm/yyyy and the formulas perked right up and worked.

Well thanks for the education. Usually when I deal with helping somebody with different date formats I just enter the data by spelling out the month the days are obvious and I use the 4 digit year.

Well this was an education, thank for that, I was thinking you were going to figure I was trying to be obnoxious, but it wasn't working for me. Now I'm going to switch my date back and watch the formulas fail, but I learned something and I appreciate it.

Thanks,
Bruce

[newdoverman]

Obnoxious.....not at all. having a problem.....maybe trying to help....positively.

I'm happy to have been of some help. All of us are always learning

When dates don't work for me with a downloaded Excel file, I select the cells and change the formatting to GENERAL if the dates change to serial numbers then I know that I am dealing with real dates even though the format may be different. The idea of spelling out the date makes perfect sense to me as it takes the confusion out of date formats.

[ooberandy]

Thanks for all you replies, I really appreciate all your help.

Indi_Ra's is the only formula that works for me though, so I'll stick with that.

Thanks