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.
<colgroup><col></colgroup><tbody>
</tbody>
<colgroup><col><col span="4"><col><col span="6"></colgroup><tbody>
</tbody>
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.
<colgroup><col></colgroup><tbody> </tbody> |
<colgroup><col></colgroup><tbody>
</tbody>
QTR 1 | QTR 2 | QTR 3 | QTR 4 | FY17 | QTR 1 | QTR 2 | QTR 3 | QTR 4 | |||
Start Date | 10/1/18 | 1/1/19 | 4/1/19 | 7/1/19 | Start Date | 10/1/17 | 10/1/18 | 1/1/19 | 4/1/19 | 7/1/19 | |
End Date | 12/31/18 | 3/31/19 | 6/30/19 | 9/30/19 | End Date | 9/30/18 | 12/31/18 | 3/31/19 | 6/30/19 | 9/30/19 | |
# Requests Obligated | 0 | 0 | 0 | 0 | # Requests Approved | 1 | 0 | 0 | 0 | 0 | |
Obligated: | 0 | Approved: | 1 | ||||||||
1 |
<colgroup><col><col span="4"><col><col span="6"></colgroup><tbody>
</tbody>