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

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

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,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...
  • Auto-Create a monthly Sign in sheet for preschool students
    The image below is what each page looks like. Above is space for the "Child Name" "Month" "Class" School days are obviously Monday-Friday but...
  • VBA vlookup multiple results
    Hi folks, Hopefully someone out there can help. I have a list to vlookup which works (ish). the lookup only picks up the first instance of the...
  • Extract values for earliest/latest times
    I am trying to put together a formula to get the earliest start time, the latest end time from column A for each person in Column B-F without the...
Top