Multiple Countif

[kmrowlan]

I am attempting to do a multiple countif statement but think I need to go another direction. Currently I have:

=COUNTIF(C53:C58,">="&D1020)-COUNTIF(C53:C58,">"&D1021)

Which counts if the date is between two time frames but I need to added one more variable from another column. I need to count if the date falls within that range and it belongs to a particular department.

Any suggestions?

 

[Jonmo1]

Assuming the Department is in column B for example...

Try
=SUMPRODUCT(--(B53:B58="department x"),--(C53:C58>=D1020),--(C53:C58<=D1021))

 

[T. Valko]

I am attempting to do a multiple countif statement but think I need to go another direction. Currently I have:

=COUNTIF(C53:C58,">="&D1020)-COUNTIF(C53:C58,">"&D1021)

Which counts if the date is between two time frames but I need to added one more variable from another column. I need to count if the date falls within that range and it belongs to a particular department.

Any suggestions?

Try one of these...

For ANY version of Excel:

D1022 = some depatment

=SUMPRODUCT(--(X53:X58=D1022),--(C53:C58>=D1020),--(C53:C58<=D1021))

For Excel 2007 and later:

=COUNTIFS(X53:X58,D1022,C53:C58,">="&D1020,C53:C58,"<="&D1021)

Replace X53:X58 with the actual range.

 

[kmrowlan]

the sumrpoduct results in #VALUE! see the formula:

=SUMPRODUCT(--(G53:G55="IN Distribution"),--(C53:C58>=D1020),--(C53:C58<D1021))

and the countifs results in #name? see formula below:

=countifs(G53:G55,"IN Distribution",C53:C58,">="&D1020,C53:C58,"<="&D1021)

Can you see where I have done something wrong?

 

[T. Valko]

the sumrpoduct results in #VALUE! see the formula:


=SUMPRODUCT(--(G53:G55="IN Distribution"),--(C53:C58>=D1020),--(C53:C58<D1021))< p>
and the countifs results in #name? see formula below:

=countifs(G53:G55,"IN Distribution",C53:C58,">="&D1020,C53:C58,"<="&D1021)

Can you see where I have done something wrong?

What version of Excel are you using? It's probably not Excel 2007 or later which might be why you get a #NAME? error for the COUNTIFS version.

I can't tell what your SUMPRODUCT looks like. Some of it looks like it's missing? However, one of the ranges is not the same size.

Try this:

=SUMPRODUCT(--(G53:G58="IN Distribution"),--(C53:C58>=D1020),--(C53:C58<=D1021))

 

[kmrowlan]

Thank you....the range difference was the issue

 

[T. Valko]

Thank you....the range difference was the issue

Good deal. Thanks for the feedback!