Sumproduct

[laxjuan05]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Start Date[/TD]
[TD]3/1/2017[/TD]
[/TR]
[TR]
[TD]plan A[/TD]
[TD]3/1/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD]End Date[/TD]
[TD]4/1/2017[/TD]
[/TR]
[TR]
[TD]Plan B[/TD]
[TD][/TD]
[TD]3/15/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Plan C[/TD]
[TD][/TD]
[TD]3/1/2017[/TD]
[TD]3/22/2017[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Plan D[/TD]
[TD]5/28/2017[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hi,

I need help figuring out how to get a return value for the number of plans in Column A if there is a date between 3/1/2017 and 4/1/2017 in column B,C, or D. The answer I am trying to get is 3 since there is three plans with those dates criteria.

I am using =SUMPRODUCT((B:D>=F1)*(B:D<F2)) but that returns 4 since there is four cells with dates that meet the criteria.
Is there any way to put in a formula that would return the number of plans that are in column A no matter if there is dates that meet the criteria in two columns of the plan row?

Thank you for your help!

 

[Marcelo Branco]

Re: Sumproduct Help

Maybe...


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][td="bgcolor: #DCE6F1"]

F​

[/td][td="bgcolor: #DCE6F1"]

G​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Plans​

[/td][td]

Date1​

[/td][td]

Date2​

[/td][td]

Date3​

[/td][td]

Start Date​

[/td][td]

03/01/2017​

[/td][td]

Result​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

plan A​

[/td][td]

03/01/2017​

[/td][td][/td][td][/td][td]

End Date​

[/td][td]

04/01/2017​

[/td][td="bgcolor:#D9D9D9"]

3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

Plan B​

[/td][td][/td][td]

03/15/2017​

[/td][td][/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

Plan C​

[/td][td][/td][td]

03/01/2017​

[/td][td]

03/22/2017​

[/td][td][/td][td][/td][td][/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

Plan D​

[/td][td]

05/28/2017​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

Formula in G2
=SUMPRODUCT(--(MMULT(($B$2:$D$5>=$F$1)*($B$2:$D$5<=$F$2),{1;1;1})>0))

M.

 

[laxjuan05]

Re: Sumproduct Help

Marcelo,

Thanks for your help, this is exactly what I was looking for!

 

[Marcelo Branco]

Re: Sumproduct Help

You are welcome. Thanks for the feedback.

M.