Gesyca_is_joy
Board Regular
- Joined
- Apr 24, 2014
- Messages
- 90
- Office Version
- 365
- Platform
- Windows
Hello, I have a formula that adds the total calue of Invoices which meet several criteria. One particular Column (K) with either be blank, hold a text value or hold a number that has special formatting to always be 6 digits long.
I have this formula working for all but one variation where the column K is a number; i tried using ISNUMBER but It isn't working.
BaseFormula: =(SUMIFS(Invoices!$F$1:$F$4994,Invoices!$H$1:$H$4994,'Chart of Accounts'!$A3,Invoices!$K$1:$K$4994,"=",Invoices!$M$1:$M$4994,"="))
Explanation: Sum the values of cells in column F if the values in Column H equal the pertinent Cell in Column A of another Sheet, AND if Column K is blank AND if Column M is blank.
I have made variations of this wherein Column K will have a specific text value (i.e. "REJECTED") successfully, but now I need to edit this formula to function if Column K has a numeric value in it; however that column is formatted as "Special" because i enter a 3 digit number and it will automatically add the 3 leading zeros to equal a 6 digit number.
Unsure of how to proceed?
I have this formula working for all but one variation where the column K is a number; i tried using ISNUMBER but It isn't working.
BaseFormula: =(SUMIFS(Invoices!$F$1:$F$4994,Invoices!$H$1:$H$4994,'Chart of Accounts'!$A3,Invoices!$K$1:$K$4994,"=",Invoices!$M$1:$M$4994,"="))
Explanation: Sum the values of cells in column F if the values in Column H equal the pertinent Cell in Column A of another Sheet, AND if Column K is blank AND if Column M is blank.
I have made variations of this wherein Column K will have a specific text value (i.e. "REJECTED") successfully, but now I need to edit this formula to function if Column K has a numeric value in it; however that column is formatted as "Special" because i enter a 3 digit number and it will automatically add the 3 leading zeros to equal a 6 digit number.
Unsure of how to proceed?