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

[Rafaa]

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

	A	B	C	D	E	F	G	H

<colgroup><col style="width: 25pxpx"><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
[TD="align: center"]1[/TD]
[TD="align: center"]factor X[/TD]
[TD="align: center"]factor Y[/TD]
[TD="align: center"]Type[/TD]
[TD="align: center"]Expenses 1[/TD]
[TD="align: center"]Expenses 2[/TD]
[TD="align: center"]Expenses 3[/TD]
[TD="align: center"]Expenses 4[/TD]
[TD="align: right"][/TD]

[TD="align: center"]2[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]2[/TD]
[TD="align: center"]A[/TD]
[TD="align: center"]100[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]3[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]4[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]200[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]4[/TD]
[TD="align: center"]12[/TD]
[TD="align: center"]6[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]5[/TD]
[TD="align: center"]16[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]6[/TD]
[TD="align: center"]20[/TD]
[TD="align: center"]10[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]0[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]7[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

[TD="align: center"]8[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: center"]Total[/TD]
[TD="align: center"]300[/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]
[TD="align: right"][/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<thead>[TR="bgcolor: #DAE7F5"]
[TH="width: 10px"]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
</thead><tbody>[TR]
[TH="width: 10px, bgcolor: #DAE7F5"]D8[/TH]
[TD="align: left"]=SUM(D2:D6,SUMIF(D2:D6,ISFORMULA(D2:D6),A2:A6),SUMIF(D2:D6,AND(D2:D6>0,ISNUMBER(D2:D6)),B2:B6))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

 

[Teeroy]

The ISFORMULA function was only added in XL2013. There are some ways to get that functionality in earlier version, see http://www.mrexcel.com/forum/excel-questions/491385-formula.html.

 

[vogel997]

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

 

[Rafaa]

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.

 

[vogel997]

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]

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

 

[Teeroy]

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

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]

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