Firehazurd
New Member
- Joined
- Jun 9, 2015
- Messages
- 4
I've got an issue with how to pull out the sum product of a value (X) set based on the share of presence of A and B respectively. The main problem is that I can't actually see A and B explicitly as shown below. Instead I've got a reference that tells me that A runs from 11/4 to 12/30 and B runs from 12/9 to 2/3.
I've been working with trying to get a SUMPRODUCT formula to work, but it seems to struggle with it. I'm currently trying an array formula, but also not getting the results I know I should.
With the below example, I should be able to sum the -5 values from 11/4, 11/11 and 11/18 at a 100% rate for A, and then the -10 on 12/9 as a 50% rate for A and B. That means that A should total -20, while B totals -5.
I need to be able to accomplish this without adding any columns to the bigger table, and with the understanding that the A and B column don't actually exist, they're for visualizing purposes in this post only.
Here is the excel file I'm using as an example. https://drive.google.com/open?id=1CcNNN20GTcoxcEta-TUSfb9QydP-ZB2C
Currently, my terrible attempt is this:
<tbody>
</tbody>
<tbody>
</tbody>
</m2),($k$2>
I've been working with trying to get a SUMPRODUCT formula to work, but it seems to struggle with it. I'm currently trying an array formula, but also not getting the results I know I should.
With the below example, I should be able to sum the -5 values from 11/4, 11/11 and 11/18 at a 100% rate for A, and then the -10 on 12/9 as a 50% rate for A and B. That means that A should total -20, while B totals -5.
I need to be able to accomplish this without adding any columns to the bigger table, and with the understanding that the A and B column don't actually exist, they're for visualizing purposes in this post only.
Here is the excel file I'm using as an example. https://drive.google.com/open?id=1CcNNN20GTcoxcEta-TUSfb9QydP-ZB2C
Currently, my terrible attempt is this:
Code:
{=IF(AND(C3:C25>=$L$2,C3:C25<m2),($k$2 sumifs(k2:k3,l2:l3,"="">="&C3:C25,M2:M3,"<"&C3:C25))*E3:E25,0)}
J | K | L | M | |
2 | A | 100 | 11/04/18 | 12/30/18 |
3 | B | 100 | 12/09/18 | 02/03/19 |
<tbody>
</tbody>
C | E | G | H | |
X | A | B | ||
3 | 11/04/18 | -5 | 100 | |
4 | 11/11/18 | -5 | 100 | |
5 | 11/18/18 | -5 | 100 | |
6 | 11/25/18 | 100 | ||
7 | 12/02/18 | 100 | ||
8 | 12/09/18 | -10 | 100 | 100 |
9 | 12/16/18 | 100 | 100 | |
10 | 12/23/18 | 100 | 100 | |
11 | 12/30/18 | 100 | ||
12 | 01/06/19 | 100 | ||
13 | 01/13/19 | 100 | ||
14 | 01/20/19 | 100 | ||
15 | 01/27/19 | 100 |
<tbody>
</tbody>
</m2),($k$2>