Counting Multiple Criteria with Date and Text Values

[JERICA]

Hi to all online...

I am trying to formulate a countifs syntax since i am referring to multiple criteria..is there a particular syntax in counting a date value?

I wanted to countifs P16:P5015 column which has a date value (any date value)

only IF

O16:O5015 column has a date value

AND column N16:N5015 has an "OK" OR "NO Punchlist" value.

Result should be: 3
Thanks in advance...

[JERICA]

[aydeegee]

Hi JERICA

One way is to use a helper column. In it type

Please Login or Register  to view this content.

Copy this down to row 5015 and count the "TRUE"s

Regards
Alastair

[JERICA]

Hi JERICA

One way is to use a helper column. In it type

Please Login or Register  to view this content.

Copy this down to row 5015 and count the "TRUE"s

Regards
Alastair

Thanks for your attention aydeegee,,,, Do you mean to say that I have to use another column and hide it to get the result?

[JERICA]

[Alex H]

Hi JERICA,

Assuming all your dates are after the year 2000 another way of doing it might be the following:

=COUNTIFS(P16:P5015,">=2000",O16:O5015,">=2000",N16:N5015,"ok") + COUNTIFS(P16:P5015,">=2000",O16:O5015,">=2000",N16:N5015,"no punchlist")

Alex

[JERICA]

Hi JERICA,

Assuming all your dates are after the year 2000 another way of doing it might be the following:

=COUNTIFS(P16:P5015,">=2000",O16:O5015,">=2000",N16:N5015,"ok") + COUNTIFS(P16:P5015,">=2000",O16:O5015,">=2000",N16:N5015,"no punchlist")

Alex

Alex,,,
i wonder why its not returning a result......
i have done similar syntax before but it's not returning a result...i wonder if there is any particular formula in counting date values....

[aydeegee]

hI jerica

Do you mean to say that I have to use another column and hide it to get the result?

Yes, quite right - that's how a helper column works.

Regards
Alastair

[aydeegee]

Hi Jerica

=COUNTIFS(P16:P5015,">=2000",O16:O5015,">=2000",N16:N5015,"ok") + COUNTIFS(P16:P5015,">=2000",O16:O5015,">=2000",N16:N5015,"no punchlist")

works for me!?

Regards
Alastair

[JERICA]

Hi Jerica
works for me!?

Regards
Alastair

Thanks so much Alex, i already figured it out...to share with...i used SUMPRODUCT..i was just guessing but when i checked the results...it's like magic hehe

=SUMPRODUCT(--(YEAR(P16:P5015)=YEAR($P$16:$P$5015)*((M16:M5015="OK")+(M16:M5015="NO PUNCHLIST"))*(O16:O5015>0)*(P16:P5015>0)))

[JERICA]

Hi Jerica
works for me!?

Regards
Alastair

Thanks so much Alex, i already figured it out...to share with...i used SUMPRODUCT..i was just guessing but when i checked the results...it's like magic hehe

=SUMPRODUCT(--(YEAR(P16:P5015)=YEAR($P$16:$P$5015)*((M16:M5015="OK")+(M16:M5015="NO PUNCHLIST"))*(O16:O5015>0)*(P16:P5015>0)))