(formula to convert number into text) = number & “” or =CONCATENATE(number,””) You can concatenate numbers with “” to make them text value.
#Formula how to sum a column in excel code
Hence you need to convert city code into text. Remember wild card operators only work with text values. If didn’t know the number of characters, we would use “*”. As here we know that there are two more numbers after 1100. “?” operator is used when we know number of characters but don’t know the character. We know that all Delhi city codes start from 1100. Hmmm… So what do we have? Aha! City code. In the third question, our condition is Delhi. The above SUMIF formula will return 103973 as shown in the image below. The above SUMIF formula will return 31907 as shown in the image below.įor the second question, our condition range will be D2:D10. Since SUMIF is not case sensitive, hence it doesn’t matter. Text conditions are always written in double quotes. So in cell I2 we will write: =SUMIF(A2:A10,"ravi",E2:E10) So our condition range will be name range and that is A2:A10. We need to tell the sum of the price won by Ravi.
But theory…, Ah! We will cover it later.įor this example I have prepared this data.īased on this data we need to answer these questions:
Generic Excel SUMIF Formula: =SUMIF( condition_range,condition,sum range) But that’s not what we want to do.Īs the name suggests, the SUMIF formula in Excel sums values in a range on a given condition. It sums them up and returns the result as 525.Īnother method can be having a totals column and then use it with SUMIF function to get the sum of all columns. (B2:B9=H2): This part compares each value in range B2:B9 with H2 and returns an array of TRUE and FALSE. Let’s break down the formula and understand it in pieces. This returns 525 as total amount spent on mangos. In I2 the formula is = SUMPRODUCT((B2:B9=H2)*C2:E9)
We just need to get the total amount spent on mangoes in all these months. In the above image, we have this table of the amount spent on different fruits in different months. This can have multiple columns but same rows as criteria range. Generic Formula = SUMPRODUCT((criteria_range=criteria)*(sum_range))Ĭriteria_range: This is the range in which criteria will be matched.Ĭriteria: this is the criteria or condition. This article is all about summing multiple columns on condition. But how do we sum multiple columns in one condition. We know how to sum one column on multiple conditions. For example finding the sum of price of products which belong to the household category. It lets the user have the sum of values having criteria on the corresponding text values. Whenever we need to find the sum of values having criteria, we use the SUMIF function. In this article, we will learn How to Sum using Text Characters as Criteria in Excel.