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

Rafaa

New Member
Joined
Aug 29, 2013
Messages
21
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

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</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))

<thead>
</thead><tbody>
</tbody>

<tbody>
</tbody>
 

Some videos you may like

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
Joined
Jan 22, 2010
Messages
412
factor Xfactor YTypeExpenses 1
42A100
84B200
126C0
168D0
2010E0
Total308=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
Joined
Jan 22, 2010
Messages
412

ADVERTISEMENT

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
 

theBardd

Rules violation
Joined
Jan 21, 2012
Messages
912
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.

See http://www.xldynamic.com/source/xld.sumproduct.html
 

Watch MrExcel Video

Forum statistics

Threads
1,112,780
Messages
5,542,484
Members
410,555
Latest member
rebecamuresan
Top