Good day!
What I'm trying to achieve on the example below is to show on cell D8 the sum of the values on column D plus a constant factor from either column A or B, depending if the value in D is a Formula or a Number.
If the value in D is a formula, I have to add to the total the correspondent value in A and if the value is a number, I have to add the correspondent value from B instead.
For example, on cel D2 I have the formula =50+50, so I should add to the total the value in A2 (4). While in cell D3, I have a number 200, so I should add to the total the value in B3 (4).
I'm trying to obtain this total using the following formula. But it is not working. I trying to use different criteria and the logic works, but it stops working when I try to use the formulas ISFORMULA and ISNUMBER as the criteria.
=SUM(D2:D6,SUMIF(D2:D6,ISFORMULA(D2:D6),A2:A6),SUMIF(D2:D6,AND(D2:D6>0,ISNUMBER(D2:D6)),B2:B6))
If you can see what I'm doing wrong or even if you know a different way to obtain the same results, I would appreciate your assistance!
Thank you very much in advance.
Best regards,
Rafa
Excel 2012
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
<tbody>
</tbody>
What I'm trying to achieve on the example below is to show on cell D8 the sum of the values on column D plus a constant factor from either column A or B, depending if the value in D is a Formula or a Number.
If the value in D is a formula, I have to add to the total the correspondent value in A and if the value is a number, I have to add the correspondent value from B instead.
For example, on cel D2 I have the formula =50+50, so I should add to the total the value in A2 (4). While in cell D3, I have a number 200, so I should add to the total the value in B3 (4).
I'm trying to obtain this total using the following formula. But it is not working. I trying to use different criteria and the logic works, but it stops working when I try to use the formulas ISFORMULA and ISNUMBER as the criteria.
=SUM(D2:D6,SUMIF(D2:D6,ISFORMULA(D2:D6),A2:A6),SUMIF(D2:D6,AND(D2:D6>0,ISNUMBER(D2:D6)),B2:B6))
If you can see what I'm doing wrong or even if you know a different way to obtain the same results, I would appreciate your assistance!
Thank you very much in advance.
Best regards,
Rafa
Excel 2012
A | B | C | D | E | F | G | H | |
---|---|---|---|---|---|---|---|---|
1 | factor X | factor Y | Type | Expenses 1 | Expenses 2 | Expenses 3 | Expenses 4 | |
2 | 4 | 2 | A | 100 | ||||
3 | 8 | 4 | B | 200 | ||||
4 | 12 | 6 | C | 0 | ||||
5 | 16 | 8 | D | 0 | ||||
6 | 20 | 10 | E | 0 | ||||
7 | ||||||||
8 | Total | 300 |
<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>
Sheet1
Worksheet Formulas
<thead> </thead><tbody> </tbody> |
<tbody>
</tbody>