Changing range based on Date
 

I am counting cells with multiple criteria with the SUMPRODUCT function.

What I would like to do is have the function's 2nd range change as the
current date changes.

IE: If it is 1 Oct it will pull from column B, and then on 2 Oct it pulls
form column C and so on.

How can I change the 2nd range to match the column with the current day?



A B C D
1 | 1 Oct 2 Oct 3 Oct
2 | 7 X X
3 | 7 X
4 | 5 X X
5 | 5 X X X
6 | 5 X


# of 7 =SUMPRODUCT((A2:A6="7")*(B2:B6="X"))
# of 5 =SUMPRODUCT((A2:A6="5")*(B2:B6="X"))

Changing range based on Date
 

Try the below with the dates in row1. and the dates in excel/date format...

=SUMPRODUCT((A2:A6=7)*(INDIRECT(ADDRESS(2,MATCH(TO DAY(),1:1,0)) & ":" &
ADDRESS(6,MATCH(TODAY(),1:1,0)))="X"))

If this post helps click Yes
---------------
Jacob Skaria


"KeyloPapa" wrote:

I am counting cells with multiple criteria with the SUMPRODUCT function.

What I would like to do is have the function's 2nd range change as the
current date changes.

IE: If it is 1 Oct it will pull from column B, and then on 2 Oct it pulls
form column C and so on.

How can I change the 2nd range to match the column with the current day?



A B C D
1 | 1 Oct 2 Oct 3 Oct
2 | 7 X X
3 | 7 X
4 | 5 X X
5 | 5 X X X
6 | 5 X


# of 7 =SUMPRODUCT((A2:A6="7")*(B2:B6="X"))
# of 5 =SUMPRODUCT((A2:A6="5")*(B2:B6="X"))

Changing range based on Date
 

=SUMPRODUCT((A2:A6=7)*(B1:D1=TODAY())*(B2:D6="x"))


"KeyloPapa" wrote:

I am counting cells with multiple criteria with the SUMPRODUCT function.

What I would like to do is have the function's 2nd range change as the
current date changes.

IE: If it is 1 Oct it will pull from column B, and then on 2 Oct it pulls
form column C and so on.

How can I change the 2nd range to match the column with the current day?



A B C D
1 | 1 Oct 2 Oct 3 Oct
2 | 7 X X
3 | 7 X
4 | 5 X X
5 | 5 X X X
6 | 5 X


# of 7 =SUMPRODUCT((A2:A6="7")*(B2:B6="X"))
# of 5 =SUMPRODUCT((A2:A6="5")*(B2:B6="X"))

Changing range based on Date
 

Try this:

=SUMPRODUCT(--(A2:A6=7),--(INDEX(B2:D6,,MATCH(TODAY(),B1:D1,0))="X"))

--
Biff
Microsoft Excel MVP


"KeyloPapa" wrote in message
...
I am counting cells with multiple criteria with the SUMPRODUCT function.

What I would like to do is have the function's 2nd range change as the
current date changes.

IE: If it is 1 Oct it will pull from column B, and then on 2 Oct it pulls
form column C and so on.

How can I change the 2nd range to match the column with the current day?



A B C D
1 | 1 Oct 2 Oct 3 Oct
2 | 7 X X
3 | 7 X
4 | 5 X X
5 | 5 X X X
6 | 5 X


# of 7 =SUMPRODUCT((A2:A6="7")*(B2:B6="X"))
# of 5 =SUMPRODUCT((A2:A6="5")*(B2:B6="X"))

Changing range based on Date
 

Perfect. Thank you. All three answers worked.



"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A2:A6=7),--(INDEX(B2:D6,,MATCH(TODAY(),B1:D1,0))="X"))

--
Biff
Microsoft Excel MVP


"KeyloPapa" wrote in message
...
I am counting cells with multiple criteria with the SUMPRODUCT function.

What I would like to do is have the function's 2nd range change as the
current date changes.

IE: If it is 1 Oct it will pull from column B, and then on 2 Oct it pulls
form column C and so on.

How can I change the 2nd range to match the column with the current day?



A B C D
1 | 1 Oct 2 Oct 3 Oct
2 | 7 X X
3 | 7 X
4 | 5 X X
5 | 5 X X X
6 | 5 X


# of 7 =SUMPRODUCT((A2:A6="7")*(B2:B6="X"))
# of 5 =SUMPRODUCT((A2:A6="5")*(B2:B6="X"))

Changing range based on Date
 

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"KeyloPapa" wrote in message
...
Perfect. Thank you. All three answers worked.



"T. Valko" wrote:

Try this:

=SUMPRODUCT(--(A2:A6=7),--(INDEX(B2:D6,,MATCH(TODAY(),B1:D1,0))="X"))

--
Biff
Microsoft Excel MVP


"KeyloPapa" wrote in message
...
I am counting cells with multiple criteria with the SUMPRODUCT function.

What I would like to do is have the function's 2nd range change as the
current date changes.

IE: If it is 1 Oct it will pull from column B, and then on 2 Oct it
pulls
form column C and so on.

How can I change the 2nd range to match the column with the current
day?



A B C D
1 | 1 Oct 2 Oct 3 Oct
2 | 7 X X
3 | 7 X
4 | 5 X X
5 | 5 X X X
6 | 5 X


# of 7 =SUMPRODUCT((A2:A6="7")*(B2:B6="X"))
# of 5 =SUMPRODUCT((A2:A6="5")*(B2:B6="X"))