This is the range from which you want to count the non-blank cells. Sumifs <> not operating as an AND function. e. The result, again. sum_range (optional): This is the range of cells containing the values you want to sum. A common use case for the NOT function is to reverse the behavior of another function. Here is the original formula: =SUMIFS(D$2:D2,B$2:B2,B2,C$2:C2,C2) With this formula, we will sum the amounts in Column B provided the corresponding Cell in Column A is not blank. In this example, the function sums units sold, if the value in the Clothing Item column equals Pants. To sum certain cells when other cells are not blank, use the SUMIF function, which is especially designed for conditional sum. Greetings. If it is not empty, then Value_If_Not_Empty is returned. In the example shown, cell G5 contains this formula: = SUMIF (D5:D16,"<" & F5) With $1,000 in cell F5, this formula returns $3,875, the sum of values in D5:D16 less than $1,000. In the example shown, the formula in cell I5 is: =SUM(SUMIFS(data[Total],data[Color],{"red","blue"})) Where data is an Excel Table in the range B5:F16. Is it even possible? Thanks! You wont be able to say "not blank" in a SUMIF but you can SUMIF it is. Using Helper Column with LEN and TRIM Functions You can use the following formulas in Excel to sum a range of cells if the value in a corresponding range is not blank: Formula 1: Sum If Not Blank (One Column) =SUMIF(A:A, "<>", B:B) This formula sums the values in column B only where the values in column A are not blank. If every value in the range is blank, then the formula simply. I have 3 columns I need to look at to add certain numbers in my 4th column, and the third criterion column must be blank in order to add the number in the 4th cell. 3. And this happens not only with SUMIF, but with other formulas as well. 0. 0. The syntax of the FILTER function is as follows: FILTER (array, include, [if_empty]) Where: Array (required) - the range or array of values that you. The result from COUNTIFS is 2, since there are two empty cells in Group A. However, I want the sum column to show blank instead of 0 if all of the other columns are blank. 0. Hello, I need your help - we have 2x columns "Outage Durations" & "C_Durations" we would like to calculate the Sum of column "C_Durations" 1st if the column/row is not Blank. However, you can also use the SUMPRODUCT. 20 in a cell and use the cell reference in the SUMIF formula as below. ## 0. many thanks. I have 3 columns I need to look at to add certain numbers in my 4th column, and the third criterion column must be blank in order to add the number in the 4th cell. 3 Answers. For example, the formula =SUMIF (B2:B4, "", C2:C4) sums only the values in the range C2:C4, where. Current formula is below, just returns a FALSE. However, if I were to input "=A1+A2" into A27 (where A1 and A2 are not visible on screen) and press 'Enter', the cell will remain blank until I double click it or perform. Using SUMIF on a range of columns or SUMPRODUCT to ignore text items. New quick method: Select Thread Tools-> Mark thread as Solved. You could differentiate if you want to hide all zeroes from the entire worksheet or just from selected cells. The SUMIFS Function sums data that meets certain criteria. I am taking the sum of a range of cells where a SUMIF condition is met. SUMPRODUCT (ISNUMBER ( range )*1)>0. To sum numbers in one column depending on whether a cell in another column is empty or not empty, use one of the following criteria: "=" to sum cells that are completely blank, i. Excel SUMIF: If text is empty, not empty (blank, not blank) As you can see in the following screenshot, some orders have notes while others. Sum Values Based on Non-Blank Cells. e. If you need to sum cell values above until blank cells, the following VBA code may do you a favor. Otherwise, it returns Not blank. "<>" to sum non-blank cells including zero length strings. Here is my sample data that i am trying to conditionally sum in EXCEL 2007. 4. 2,303. Sum_Range is entered last in the SUMIF functio n. If you want, you can apply the criteria to one range and sum the corresponding values in a different range. So, on that evidence, you then evaluate: 12. If you want help with that, provide some example formulas. End the formula with a closing parenthesis ) and then press Enter. Note that SUMIFS is not case-sensitive. I currently have =SUMIF (H2:H1768,"<>",S2:S1768) but I can tell it's not returning correctly. First of all open your worksheet where you need to add the cells based on background colors. Excel sumif only when all are non blank cells. Suppose we have the following dataset in Google Sheets that contains information about. Alternatively you can use conditional formatting to paint the cell white if the cell is blank or not a number. Sometimes we need to know the sum of certain numeric data even though there may be errors in it. Share. Notice again that SUMIF is not case-sensitive. Example: Use “If Not Empty” Formula. The exact place to find this option varies between versions of Excel so you'll need to tell us which vesrion you are using (or search its help for "zero values" or similar). If you must make such cells blank: copy the B. In the Function Arguments dialog, please specify the range you will count colored cells within in the Reference box, choose the cell that is filled by the specified background color in the. 1 Returning Text If Cell Is Blank. To sum values less than a given value, you can use the SUMIF function or the SUMIFS function. Now, you have to scroll down and click on “SUMIF”, which is highlighted in Red. See moreHow to Use SUMIF Function to Sum Not Blank Cells in Excel 1. Sum if not blank. ; As you see, the syntax of the Excel SUMIF function allows for one condition only. =SUMPRODUCT (NOT (ISBLANK (B3:B7))*C3:C7) It adds numbers and returns a total if the corresponding value in B3:B7 is not a blank cell. The SUMIF function syntax has the following arguments: range Required. I have a project which I am really struggling with. Formula in cell D3: =COUNTIF (B3:B13,"<>") The first argument in the COUNTIF function is the cell range where you want to count matching cells to a specific value, the second argument is the value you want to. =SUMIF (A1:A6,"<20”) You can also keep the criteria value, i. Formula should be able to sum until blank column in first row or until 1 in helper row. Let’s follow the instructions below to learn. This will of course return an . The resulting output will be like. The SUMIF Function: an Overview. For cell A4, the formula returns. The current Range is B2:B30, it gives me the number 29 even though the cells are blank. The selected range may contain dates in standard Excel format (examples below). To show the sum when all/some of the cells contain numbers, I would do: =IF(COUNT(A1:E1)>0,SUM(A1:E1),"")If you just want to sum two cells only if they both have a numerical value you can use the function. Sum if not blank: "<>" to add up cells that contain any value, including zero length strings. If you really want to use SUMIF as way to count the empty cells, use something like this: =COUNT(E3:E36)=SUMIF(E3:E36, "1") - but obviously, using COUNTIF is a better approach. The first function we think of is IF function, when it comes to conditional output. Custom functions are slow. After that, in the first argument, refer to the range A1:A10. In this example, we will use IF and OR function together. Otherwise, Value_If_Empty is returned. If not blank then sum How to use: Sum if is not blank. How To Ignore Blank Cells for a Formula. We can manually verify this by taking the sum of the points where the player value is not blank: Sum of Points: 4 + 5 + 5 + 10 + 10 + 5 + 6 = 45. This condition is important, because some cells are empty, some have random strings in it, some are marked with "N. Probably the easiest option is to just not display 0 values. Someone emailed to ask how they could ignore one criterion in a SUMIFS formula, if that cell is empty. When you attempt to use the Excel SUM() function with a range of data that contains an #N/A error, Excel will raise the same #N/A errors. I want only to sum the cells where the cell by its side (A cell) is not zero or blank. If so, it also results in an. "" : returns an empty string blank cell instead of Zero. In this section, you will learn how to sum values where the adjacent cells contain texts in Excel. Excel formula: if cell is not blank, then sum. SUMIF(cell_range, criteria, sum_range) where the first two arguments are required. If omitted, the range is used for summation. Hi i have this formula in b9:e9 =SUMIF(D3:D9,"",C3:C9) but it is returning zero when the cells are blank. Description. This is because the function accepts only numeric values as inputs. If you need to remove blank. Select the blank cell in the row below the cells that you want to sum, cell A5 in this example. B. You can use the sum_range argument for adding cells in a range other than. If they entered values into the column (even if the values are 0 all the way down the column), then they have completed the task. In the end, enter the closing parentheses and hit enter to get the result. I'm looking for a way to make sum only if one of both cells are not blank otherwise leave the sum cell blank. e. The first expression subtracts 30 minutes from every time in the named range. When applied to a cell range containing text strings, the SUM function returns, and so does the COUNT function. It should be: =SUMIF (C2:C5,"<>",B2:B5) Share. I use this formula: =SUMIF (B:B,"<>",A:A) but it not understand that "" is blank. To create a conditional sum with the SUMPRODUCT function you can use the IF function or use Boolean logic. Now it's seen as a blank string and not an empty cell so Excel won't treat it like a zero. In that top box just type:. Conditional Formatting. I want to sum the values of column B where column A has a blank field. As the formula is copied down, it returns TRUE when all cells between columns C and G are empty, and FALSE if not. 0. This scoring is only applicable to Risks recorded on the project RAID log so, if an issue is recorded scoring will not be applied therefore, I would want the score to remain blank. Here,. The current Range is B2:B30, it gives me the number 29 even though the cells are blank. I've written a DAX measure to show percentage price increases - see screen shot. In the case of an empty string, the ISBLANK function returns a “false” value. Use the formula: =SUMIF (B:B,"<>",A:A) Explanation: The function checks the cells which are not blank and excel if the cell is not blank, it records the value of the corresponding cell. I tried this formula: =SUMIFS (CHILDREN (Amount25)), (CHILDREN ( [Date Paid]25), NOT BLANK), but it did not work. not empty. Applying “Not Blank” Operator 2. I still want the column to show a running total. Unfortunately, there is no formula way to result in a truly blank cell, "" is the best formulas can offer. SUM Excel column if row not blank, otherwise take value from left cell. In our case discussed at the beginning, we need to enter =IF (B2<>"", (TODAY ()-B2)/365. For <>, if the value is not empty it matches any cell content except the value, including empty cells. In the above formula, you have used SUMIFS but if you want to use SUMIF you can insert the below formula in the cell. "" to sum empty cells including zero length strings. Explanation: ISBLANK : function checks the cell D5. =SUMIFS(C3:C9,B3:B9,"<>"&E3) Start with the logical operator within double quotes (“<>”) and use the & symbol to join the operator with the cell. Select the cell where you want the result of the sum to appear ( C2 in our case ). Next, in the “criteria” argument, enter “<>” to tell the formula to consider the non-blank cells only. The evaluation range is first and the sum range is last. Meaning your criteria are E2 and 0. g. Clicking on it opens the Insert Function box, displaying all the built-in functions Microsoft provides in a. How to Use SUMIF Function to Sum Not Blank Cells in Excel 1. In the example shown, the formula in cell F5 is: =SUMIFS(C5:C16,B5:B16,"*hoodie*") This formula sums the quantity in column C when the text in column B contains "hoodie". Sum value only if below cell is not empty. 1 Answer. I hadn't realised that SUMIFS did not recognise a blank cell as a 0 for the purposes of testing a numeric criterion. <tbody>. In the example shown, the formula in cell F5 is: = AVERAGEIFS ( price, group,"<>") Where price (C5:C16) and group (D5:D16) are named ranges. For example, SumCellsByColor (E18)+SumCellsByColor (F19) Use the custom functions from the article above. Counting and summing colored cells in Excel; SUMIF function in Excel for numbers, dates, text, blanks and not blanks; How to use Excel SUMIFS and SUMIF with multiple criteria - formula examples. SUMIFS is not case-sensitive. It gives the same result as above. For example, this formula tests for Software and blank customer names: =SUMIFS(G2:G21, A2:A21, "Software", E2:E21, "") To sum non-blank cells in a. 4 3 4 BLANK 1 2 5 7 1 BLANK 2 1 4 BLANK 3 In this case the cell called "BLANK 1" would be the sum of the 3 previous rows: 4+3+4=11 "Blank 2" would be 15 and so forth. RJB78 Board Regular. For example, If cell A1 is blank (empty), the ISBLANK function will return TRUE: =ISBLANK(A1) // TRUE if A1 is. In B1, i want the following - Starting from A1, sum up the entries until the first blank cell is encountered. Subscribe to RSS Feed; Mark Discussion as New;. Stack Overflow. The result is 354,575, the average of Prices in C5:C16 when corresponding cells in D5:D16 are not. In our examples, you need to sum Amounts where delivery dates are mentioned in Delivery. If omitted, the range is used for summation. e. . I thought SUMIFS was the right approach, but it appears as though using "=" to only consider blank cells for the equation doesn't work (it always returns 0 instead of summing values). To see if cell B1 is blank, write the formula = B1 = “”. A good way to solve this problem is with the SUMIFS function. It appears that you want the result to appear as an EMPTY TEXT (“”), if the sum range solely consists of blank cells. To fix this, either use ranges or cells as references inside the formula. Download the featured file here: this video I demonstrate how to ig. Now, in the sum_range refer to the Quantity column. 0. Sumif adjacent cell containing text in Excel. The first range contains a few empty cells. 2. To test if all cells in a range are empty, you can use a formula based on the COUNTA or SUMPRODUCT functions. Each condition is provided with a separate range and criteria. Using Helper Column with LEN and TRIM FunctionsYou can use the following formulas in Excel to sum a range of cells if the value in a corresponding range is not blank: Formula 1: Sum If Not Blank (One. 3. To make XLOOKUP display a blank cell when a lookup result is blank, you can use a formula based on LET, XLOOKUP, and the IF function. =INDEX (D:D, AGGREGATE (15, 6, ROW ($1:$9999)/ ( (A$1:A$9999="Jan")*SIGN (LEN (D$1:D$9999))), 1)) However, if 2003 is in a cell and you have to use it to determine which column to retrieve, it gets a little more. Copying the formula in cell G3 into cell G4 returns the value $ 7,500, which is the sum of orders with green background color. I want to be able to sum the amount of hours people have actually worked i. Row 3 meets the criteria. contain absolutely nothing. Rows 1 & 3 meet the criteria. Similarly, we have to. In the example shown, the formula in cell H5 is: = SUMIFS ( data [ Amount], data [ Amount],">" & F5, data [ Amount],"<=" & G5) Where data is an Excel Table in the range B5:D16. Attention - ExcelForum Rules have been updated as of August 2023. 1. Improve this answer. Next, press ALT + F11 to open the VB Editor. QUOTIENT: Returns one number divided by another,. Conditionally sums the contents of cells in a range. To sum value if cells are not blank you can apply the Excel SUMIF function. See also: 50 examples of formula criteria. Because the first argument is blank, evaluation continues with the next argument until a non- blank value and non-empty string is found. The result is $205, the sum of Total where the Color is "Red". Take the above screenshot data as an example, to sum the total. of what this would look like in Excel: A2 = 2020-08-19, B2 = *B2 is blank. It counts the cells that have some values – texts, numbers. 0. Combine SUMIFS and COUNTIFS Functions to Calculate Average If Cell Is Not Blank. The SUMIFS function is a widely used function in Excel and can be used to sum cells based on dates,. Note: COUNTA will count both numbers and text values in. "" to sum empty cells including zero length strings. (Note: I don't want to. Format Cells dialog box will appear, go to custom tab. Stack Exchange Network. Sums units sold based on the item. Here is my formula for the moment: =SUM(L6;+K7;-J7) the same formula will be on 90 cells on the same column. Just click Enter and the number of the cells that have values in it will. Hi all, I'm struggling to write this function. Result. range: The range of cells you want to test the criteria against. Formula. To sum numbers when cells are not equal to a specific value, you can use the SUMIF or SUMIFS functions. SUMIFS has some other quirks, which are detailed in this article. Using Numbers. 0. Sum Amount greater than the Condition. The formula would be. The formula thats inside each cell is an IF statement that analyses some adjacent columns, returning a priority value when two columns are populated: Code: =IF (AH2="",AF2,AH2) Thanks. Supposing you have a range of cell values as following screenshot shown, you can apply this array formula to sum the range with ignoring errors. I'm playing around with the sumif and sumifs function but I can't seem to figure it out. Using SUMIF with Blanks. The group of cells to sum, assuming they meet all criteria. Hope there is formula to get this result. In the example shown, the formula in cell I5 is: =SUMIFS (F5:F16,C5:C16,"red") When this formula is entered, the result is $136. From our dataset, we can easily do that. It is used to scan through a range of cells checking for a specific criterion, and then adding up values in a range that correspond to those values. For the quick answer see Mr_Nitrogen's answer. Here we have two option, First find out the total of cells which are not blank and then deduct the same from total sum. The syntax of SUMIF function in Google Sheets is same as the Excel syntax; =SUMIF (range, criterion, [sum_range]) Where, range: It is a cells range that is tested against a criterion. Maybe not, but I had fun working on this SUMIFS formula with empty criteria cells! SUMIFS Formula With Empty Criteria. Example #2 - Not blank. I have tried to use the SUMIF function but not sure what to use as the criteria to be blank. If anyone could help! Sincerely!Plus you can add values in a different cell range based on the criteria in another. 2. 1. Output: Summing cells using SUMIF based on background colorTo get a count of values between two values, we need to use multiple criteria in the COUNTIF function. g. In the example below, the positive values show the cell color, negative values are red and the zero values are blank. Entering the formula using SUMIF to sum cells based on background color. I mean in cases Y column has blank value is not depend on X column. A8 - 24. g. The generic syntax for SUMIF looks like this: = SUMIF ( range, criteria,[ sum_range]) The SUMIF function takes three arguments. 1. 2. excel; vba; Share. To sum if cells contain a specific text, you need to use a wildcard when specifying the criteria in the SUMIF function. If a helper column is not appropriate for your needs, then you can use the SUMPRODUCT Function in combination with the LEN and TRIM Functions to sum. . "<>": The. We can create the following macro to do so:Ignoring empty cells. Check the Screen Shot below: N. This scoring is only applicable to Risks recorded on the project RAID log so, if an issue is recorded scoring will not be applied therefore, I would want the score to remain blank. Or you can use this way:For a new thread (1st post), scroll to Manage Attachments, otherwise scroll down to GO ADVANCED, click, and then scroll down to MANAGE ATTACHMENTS and click again. Steps: Select a cell where you want to have the summation (i. As an addendum, OP can make it work like they. In this section, you will learn how to sum values where the adjacent cells contain texts in Excel. To sum values when corresponding cells are not blank, you can use the SUMIFS function. Instead I prefer COUNTBLANK, which will count "" as blank, so basically =COUNTBLANK(C1)>0 means that C1 is blank or has "". In the example shown, the formula in cell G5 is: =SUMIFS(C5:C16,B5:B16,">="&E5,B5:B16,". Here are two ways to test for blank cells. will return 1, suggesting that that numeric is "less than or equal to" a null string, whereas: =COUNTIF (A1,"<=""") which one would hope to be equivalent to the above expression, in fact returns 0, suggesting that that numeric is, on the contrary, "greater than" a null string. If not blank multiple cells: IF ISBLANK NOT XLOOKUP: If NOT this or that: IF OR NOT: If this AND that: IF AND: If this AND that OR. , non-blank cells and discount more than 20%), we can use the COUNTIFS function. To sum numbers in one column depending on whether a cell in another column is empty or not empty, use one of the following criteria: "=" to sum cells that are completely blank, i. 2 people found this reply helpful. We don't need to enter a sum_range. Conversely, a non-zero result thereof is equivalent. For example, here, I want to sum all cells which adjacent cells are not equal to the text “Apple”, the following formula can help you. If E7 (see . Coalesce ( Blank (), 1 ) Tests the return value from the Blank function, which always returns a blank value. The above method helps you sum values where the adjacent cell is blank in a certain range. Note that "=0" does not match empty cells. This formula will sum all those amounts in range C2:C11 where cells are Not Blank in range D2:D11. It can be expressed as a number, text, logical expression, or cell reference. Enter this formula into a blank. For example the formula =SUMIF(C1:C10; "<>"; A1:A10) returns the sum of all values in the range A1:A10 if there are no empty cells in the range C1:C10. SUMIFS: Sum if not blank: SUMIFS SUMPRODUCT FILTER SUM: Sum if one of many things: SUMIFS SUMPRODUCT ISNUMBER MATCH FILTER: Sum if x or y: SUMIF SUMPRODUCT ISNUMBER MATCH:. To sum numbers if values in a criteria range begin with specific text, you can use the SUMIF function or the SUMIFS function. A5 - 30. This should display the sum of positive numbers in cell C2. The difference is that the SUMIF function sums only those values that meet the criteria you specify. We can manually verify this by taking the sum of the points where the player value is not blank: Sum of Points: 4 + 5 + 5 + 10 + 10 + 5 + 6 = 45. e. 1. Say SUMPRODUCT (1/A1:A999) ignoring blanks is what you want. jht wrote: > How to sum cells and ignore the #div/0! 's ? IMHO, it is best to avoid the #DIV/0 errors in the first place. A conditional function performs an action or calculation based on a test of data using an IF statement. Blank cell as criteria for SUMIFS. Otherwise, “Does Not Exist” is returned. In the example shown, the formula in F6 uses COUNTA like this: = COUNTA (C5:C16) // returns 9. One of those conditions is if the cell is blank, then I want the value of another cell considered for the sum, if it is not blank, then I want it excluded. To solve this problem, you can simply use the sum formula along with custom formatting to display a blank space instead of zero. Your condition results in the SUM not being calculated unless you have values in all three cells. The SUMIFS formula to sum if a cell is not blank in Google Sheets: =SUMIFS(C2:C9,B2:B9,"<>") It is important to note that just for summing cells for a single condition as we see here, the SUMIFS function is being underutilized . Not an expert on sheets (so this may somehow identical to marikamitsos's answer, though their solution did not work for me for 0-valued rows) I have done this in Google Sheets recently. Perform this for 8 different ranges spanning 42 columns. I would love help on this! FORMULA =SUM (IF (MONTH (B2:B30)=1,1)) When I put in dates anywhere in that range it does bring the count down below 29 as if its counting blank cells. 10,286. I tried this formula: =SUMIFS (CHILDREN (Amount25)), (CHILDREN ( [Date Paid]25), NOT BLANK), but it did not work. So, with 9 cells it would look like this:I'm assuming your existing range starts in cell A1 (i. The SUMIF is being done on all the values from row 2 to the row in which the formula is being placed. Hide zero numbers in Excel within the worksheet settings. QUOTIENT: Returns one number divided by another, without the remainder. display nothing) based on a conditional test, you can use the IF function with an empty string (""). For example, the simplest SUMIF formula =SUMIF (A2:A10,">10") adds the values in cells A2 to A10 that are larger than 10. There is a formula can help you quickly sum up the column ignore #N/A. It is used to search for finding zero. Formula with SUMIF to SUM Values Cells which are Not Blank. We want to sum all amounts from column D that are greater than the lower limit ($500). 1. Here are the steps to use Sumif in a text and number column in Google Sheets. Sum / Average ignore negative values with formulas. However, in Excel, the term "not empty" is a bit ambigious: if a cell contains a formula, but the result is a null string, i. sumif doesn't run "not" when cell is blank. Thus, the result is 0 when the sum is completed. Here is the formula I currently have: =IF(ISBLANK(BM2:BQ2),"","Major Milestone Due") It is returning everything as Major Milestone Due, even if there are certain row ranges with all blank cells. ”. the word "Time" exists in cell A1). 6 Ways to SUM with IF Condition in Excel. Note that we could also use the following formula to only sum the points values where the player name is blank:To make your own similar Google Sheet COUNTIF not blank formula, here’s what you’d do: Click an empty cell. The formula worked in Mac Excel but Apple Numbers . In this case we're using IF with the ISBLANK function: =IF (ISBLANK (D2),"Blank","Not Blank") Which says IF (D2 is blank, then return "Blank", otherwise return "Not Blank"). Method-8: Highlighting Blank Cells with Excel Conditional Formatting. Next, in the criteria argument, use “>0”. Excel SUMIF: If text is empty, not empty (blank, not blank) As you can see in the following screenshot, some orders have notes while others don’t. In this example, the goal is to sum amounts C5:C16 when the date in B5:B16 is greater than the date provided in cell E5. As a result, the sum of orders in cell G3 is $ 3,700. Method-5: Applying the IF and COUNTIF Functions to Return Value If Cell is Blank. Modified 6 years, 1 month ago. For example, if I were to input "=A1+A2" into A3 and press 'Enter', the answer would appear. IF + SUBTOTAL to dynamically summarize data. Excel Formula - Sum in some Column If Cell in The Same Row in Different Column is NOT Blank. Navigate to ‘Insert’ > ‘Module’. Criteria_range1 (required). The range intentionally includes blank lines above and below each block of data, to ensure that Excel will adjust for any lines that you insert or delete. I need to sum the product of 2 and 2 columns IF they have data. @Gareth: No. Here is the formula I've been trying to use: The total column says 0 although there are numbers that should be added. I am trying to sum only the values of the children in one column for which the corresponding cell from another column is not blank. sum the cells of each row which are formulas. Method #2 – Using COUNTA Function. After applying the sum formula, select all the cells which contain the sum formula and. We can use the SUMIFS Function to sum all Scores for Players with non-blank names using criteria (“<>”). The AND function returns TRUE if both are blank, and it returns FALSE if both or one of them is not blank. For the logical test, we are using the COUNTIF function like this:. criterion_range1 — The group of cells to be evaluated by the criterion. - sum) only if the source is not blank,other wise return blank !!not zero!! in excel, wps. Last but not least, we will merge the SUMIFS and COUNTIFS functions to calculate an average if the cells are not blank.