montyfern
Board Regular
- Joined
- Oct 12, 2017
- Messages
- 65
Good Morning,
Thanks in advance for your help.
I have a complex sumproduct that totals unique values in a date range. It adds the request that are obligated and the requests that are approved: =SUMPRODUCT(IF((''!N2:N79conditionxyz<=C11)*('conditionxyz'!N2:N79>=C10), 1/COUNTIFS('conditionxyz'!N2:N79, "<="&C11, '
Now, I need to provide the intersection of the two results of these date formulas. Can you help please? I've used countif's and sumif for values >0 but it's three separate formulas. Have tried index and match, but not sure what I'm doing. I please need a formula to provide the intersection of two results, the number of uniquevalues of Approved Requests within a specified date range (QTR) that are alsounique values of Obligated Requests within a date range. I used =countif(c12:f12)"1") and =countifi12:m12,"1") and get 1 for approved requests, then I use =sumif(c13:m13">0",c13:m13) to get 1 again but can I combine these 3 formulas? They won't use pivot tables so please don't suggest that.[TABLE="width: 106"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 106"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Thanks & Regards,
Patricia
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1257"]
<colgroup><col><col span="4"><col><col span="6"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]QTR 1[/TD]
[TD]QTR 2[/TD]
[TD]QTR 3[/TD]
[TD]QTR 4[/TD]
[TD][/TD]
[TD] [/TD]
[TD]FY17[/TD]
[TD]QTR 1[/TD]
[TD]QTR 2[/TD]
[TD]QTR 3[/TD]
[TD]QTR 4[/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD]10/1/18[/TD]
[TD]1/1/19[/TD]
[TD]4/1/19[/TD]
[TD]7/1/19[/TD]
[TD][/TD]
[TD]Start Date[/TD]
[TD]10/1/17[/TD]
[TD]10/1/18[/TD]
[TD]1/1/19[/TD]
[TD]4/1/19[/TD]
[TD]7/1/19[/TD]
[/TR]
[TR]
[TD]End Date[/TD]
[TD]12/31/18[/TD]
[TD]3/31/19[/TD]
[TD]6/30/19[/TD]
[TD]9/30/19[/TD]
[TD][/TD]
[TD]End Date[/TD]
[TD]9/30/18[/TD]
[TD]12/31/18[/TD]
[TD]3/31/19[/TD]
[TD]6/30/19[/TD]
[TD]9/30/19[/TD]
[/TR]
[TR]
[TD]# Requests Obligated[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]# Requests Approved[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Obligated: [/TD]
[TD]0 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Approved:[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Thanks in advance for your help.
I have a complex sumproduct that totals unique values in a date range. It adds the request that are obligated and the requests that are approved: =SUMPRODUCT(IF((''!N2:N79conditionxyz<=C11)*('conditionxyz'!N2:N79>=C10), 1/COUNTIFS('conditionxyz'!N2:N79, "<="&C11, '
conditionxyz
'!N2:N79, ">="&C10, 'conditionxyz
'!A2:A79, 'conditionxyz'
!A2:A79), 0))Now, I need to provide the intersection of the two results of these date formulas. Can you help please? I've used countif's and sumif for values >0 but it's three separate formulas. Have tried index and match, but not sure what I'm doing. I please need a formula to provide the intersection of two results, the number of uniquevalues of Approved Requests within a specified date range (QTR) that are alsounique values of Obligated Requests within a date range. I used =countif(c12:f12)"1") and =countifi12:m12,"1") and get 1 for approved requests, then I use =sumif(c13:m13">0",c13:m13) to get 1 again but can I combine these 3 formulas? They won't use pivot tables so please don't suggest that.[TABLE="width: 106"]
<colgroup><col></colgroup><tbody>[TR]
[TD][TABLE="width: 106"]
<colgroup><col></colgroup><tbody>[TR]
[TD]Thanks & Regards,
Patricia
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 1257"]
<colgroup><col><col span="4"><col><col span="6"></colgroup><tbody>[TR]
[TD] [/TD]
[TD]QTR 1[/TD]
[TD]QTR 2[/TD]
[TD]QTR 3[/TD]
[TD]QTR 4[/TD]
[TD][/TD]
[TD] [/TD]
[TD]FY17[/TD]
[TD]QTR 1[/TD]
[TD]QTR 2[/TD]
[TD]QTR 3[/TD]
[TD]QTR 4[/TD]
[/TR]
[TR]
[TD]Start Date[/TD]
[TD]10/1/18[/TD]
[TD]1/1/19[/TD]
[TD]4/1/19[/TD]
[TD]7/1/19[/TD]
[TD][/TD]
[TD]Start Date[/TD]
[TD]10/1/17[/TD]
[TD]10/1/18[/TD]
[TD]1/1/19[/TD]
[TD]4/1/19[/TD]
[TD]7/1/19[/TD]
[/TR]
[TR]
[TD]End Date[/TD]
[TD]12/31/18[/TD]
[TD]3/31/19[/TD]
[TD]6/30/19[/TD]
[TD]9/30/19[/TD]
[TD][/TD]
[TD]End Date[/TD]
[TD]9/30/18[/TD]
[TD]12/31/18[/TD]
[TD]3/31/19[/TD]
[TD]6/30/19[/TD]
[TD]9/30/19[/TD]
[/TR]
[TR]
[TD]# Requests Obligated[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD][/TD]
[TD]# Requests Approved[/TD]
[TD]1[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Obligated: [/TD]
[TD]0 [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Approved:[/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]