# 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

Highlight Duplicates
Home, Conditional Formatting, Highlight Cells, Duplicate records, OK to add pink formatting to any duplicates in selected range.

#### 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
83
Replies
17
Views
280
Replies
17
Views
121
Replies
5
Views
54
Replies
7
Views
44

1,109,411
Messages
5,528,621
Members
409,828
Latest member
99DodgeRam

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...