Counting number of dates that occur within a week - per market

[Thomas]

Hello,

I'm looking to count the number of forecasted dates that occur in a week,
per market.

Currently the way I count them I use:

Market1 (In cells BR2 to BR19):
=COUNTIFS(C_Data!$BR2:$BR19,"<" &
DATE(YEAR(A13),MONTH(A13),DAY(A13)+7),C_Data!$BR2:$BR19,">=" &
DATE(YEAR(A13),MONTH(A13),DAY(A13)))

Market2 (In cells BR20 to BR57):
=COUNTIFS(C_Data!$BR20:$BR57,"<" &
DATE(YEAR(E13),MONTH(E13),DAY(E13)+7),C_Data!$BR20:$BR57,">=" &
DATE(YEAR(E13),MONTH(E13),DAY(E13)))

and so on.. A13 and E13 both reference C32 which is where I type in the
first day of the week.

This works fine, until sometone starts resorting the data on the C_Data tab,
so the dates are not matching what's in the formulas above.

For another TAB on my spreadsheet I count the total number of actualized
items using:

=SUMPRODUCT(-(LEFT(C_Data!$B:$B,2)="Market1"),-(C_Data!$BV:$BV<>""))

So I have been trying to combine the two formulas, but have been
unsuccessful so far. Can anyone help with this?

Let me know if I need to provide more information.

Thanks,
Thomas

 

[Bob Phillips]

Do you mean

=SUMPRODUCT(
--(LEFT(C_Data!$B:$B,2)="Market1"),
--(C_Data!$BV:$BV<>""),
--(C_Data!$BR2:$BR19>=DATE(YEAR(A13),MONTH(A13),DAY(A13))),
--(C_Data!$BR2:$BR19<DATE(YEAR(A13),MONTH(A13),DAY(A13)+7)))

BTW, I would never use a single unary in SUMPRODUCT, always use a double, as
it only works for an even number of conditions.


--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Hello,

I'm looking to count the number of forecasted dates that occur in a week,
per market.

Currently the way I count them I use:

Market1 (In cells BR2 to BR19):
=COUNTIFS(C_Data!$BR2:$BR19,"<" &
DATE(YEAR(A13),MONTH(A13),DAY(A13)+7),C_Data!$BR2:$BR19,">=" &
DATE(YEAR(A13),MONTH(A13),DAY(A13)))

Market2 (In cells BR20 to BR57):
=COUNTIFS(C_Data!$BR20:$BR57,"<" &
DATE(YEAR(E13),MONTH(E13),DAY(E13)+7),C_Data!$BR20:$BR57,">=" &
DATE(YEAR(E13),MONTH(E13),DAY(E13)))

and so on.. A13 and E13 both reference C32 which is where I type in the
first day of the week.

This works fine, until sometone starts resorting the data on the C_Data
tab,
so the dates are not matching what's in the formulas above.

For another TAB on my spreadsheet I count the total number of actualized
items using:

=SUMPRODUCT(-(LEFT(C_Data!$B:$B,2)="Market1"),-(C_Data!$BV:$BV<>""))

So I have been trying to combine the two formulas, but have been
unsuccessful so far. Can anyone help with this?

Let me know if I need to provide more information.

Thanks,
Thomas


__________ Information from ESET Smart Security, version of virus
signature database 3832 (20090206) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 3832 (20090206) __________

The message was checked by ESET Smart Security.

http://www.eset.com

 

[Dave Peterson]

And something that Bob didn't notice:

LEFT(C_Data!$B:$B,2)="Market1"

Will never be true. I'm not sure if you want to check the first two characters
or the entire string.

And if you're not using xl2007, you can't use the entire column in your
formulas.

 

[Thomas]

Thanks Bob! That took care of it.

Dave is also correct in regards to not (LEFT(C_Data!$B:$B,2)="Market1") not
actually working.. I was just trying to give the markets easier names,
instead of the 2-letter codes I personally use, but forgot to update the
formula complete.

Thanks again!

 

[Bob Phillips]

LOL! Didn't even notice that.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Thanks Bob! That took care of it.

Dave is also correct in regards to not (LEFT(C_Data!$B:$B,2)="Market1")
not
actually working.. I was just trying to give the markets easier names,
instead of the 2-letter codes I personally use, but forgot to update the
formula complete.

Thanks again!






__________ Information from ESET Smart Security, version of virus
signature database 3832 (20090206) __________

The message was checked by ESET Smart Security.

http://www.eset.com

__________ Information from ESET Smart Security, version of virus signature database 3832 (20090206) __________

The message was checked by ESET Smart Security.

http://www.eset.com