# How to use ISFORMULA() as a criteria for SUMIF function?

#### Rafaa

##### New Member
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
ABCDEFGH
1factor Xfactor YTypeExpenses 1Expenses 2Expenses 3Expenses 4
242A100
384B200
4126C0
5168D0
62010E0
7
8Total300

</tbody>
Sheet1

Worksheet Formulas
CellFormula
D8=SUM(D2:D6,SUMIF(D2:D6,ISFORMULA(D2:D6),A2:A6),SUMIF(D2:D6,AND(D2:D6>0,ISNUMBER(D2:D6)),B2:B6))

</tbody>

<tbody>
</tbody>

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### vogel997

##### Active Member
 factor X factor Y Type Expenses 1 4 2 A 100 8 4 B 200 12 6 C 0 16 8 D 0 20 10 E 0 Total 308 =SUM(D2:D6,SUMPRODUCT(--(ISFORMULA(D2:D6)),A2:A6),SUMPRODUCT(--(ISFORMULA(D2:D6)=FALSE),--((D2:D6)>0),B2:B6)) TRY SUMPRODUCT INSTEAD…
<colgroup><col width="64" style="width: 48pt;" span="4"> <col width="941" style="width: 706pt; mso-width-source: userset; mso-width-alt: 34413;"> <tbody> </tbody>

#### vogel997

##### Active Member

Thank you Teeroy. I didn't know this formula wasn't available before. I will make sure it works with other people in the office before sharing the spreadsheet.

vogel997, it worked!! Thank you very much. I'm now reading this site to understand how it works. (Excel SUMPRODUCT formula - Syntax, Usage, Examples and Tutorial | Chandoo.org - Learn Microsoft Excel Online).

Have a great day, guys! Thanks for your help.

Excel Magic Trick 777: SUMPRODUCT Function -- Basics To Advanced (14 Examples) - YouTube

#### Teeroy

##### Well-known Member

I'd trust that. I don't think that XLD has updated that site for a while, and I haven't come across him here, but he's still active on VBAExpress and he's taught me heaps ray:!

#### theBardd

##### Rules violation
You are right, but I think that page is still very relevant.

Replies
7
Views
119
Replies
17
Views
375
Replies
17
Views
130
Replies
5
Views
60
Replies
7
Views
50