*In this tutorial, you will find a handful of advanced formula examples that demonstrate how to use Excel's VLOOKUP and SUM or SUMIF functions to look up and sum values based on one or several criteria.*

Are you trying to create a summary file in Excel that will identify all instances of one particular value, and then sum other values that are associated with those instances? Or, do you need to find all values in an array that meet the condition you specify and then sum the related values from another worksheet? Or maybe you are faced with a more concrete challenge, like looking through a table of your company invoices, identifying all invoices of a particular vendor, and then summing all the invoice values?

The tasks may vary, but the essence is the same - you want to look up and sum values with one or several criteria in Excel. What kind of values? Any numeric values. What sort of criteria? Any : ) Starting from a number or reference to a cell containing the right value, and ending with logical operators and results returned by Excel formulas.

So, does Microsoft Excel have any functionality that can help with the above tasks? Of course, it does! You can work out a solution by combining Excel's VLOOKUP or LOOKUP with SUM or SUMIF functions. The formula examples that follow below will help you understand how these Excel functions work and how to apply them to real data.

Please note, these are advanced examples that imply you are familiar with the general principles and syntax of the VLOOKUP function. If not, the first part of our VLOOKUP tutorial for beginners is certainly worth your attention - Excel VLOOKUP syntax and general usages.

## Excel VLOOKUP and SUM - find the sum of matching values

If you work with numerical data in Excel, quite often you have not just to extract associated values from another table but also sum numbers in several columns or rows. To do this, you can use a combination of the SUM and VLOOKUP functions as demonstrated below.

**Source data:**

Suppose, you have a product list with sales figures for several months, a column per each month. The source data is on the sheet named *Monthly Sales*:

Now, you want to make a summary table with the total sales for each product.

The solution is to use an array in the 3^{rd} parameter (**col_index_num**) of the Excel VLOOKUP function. Here is a generic formula:

*lookup value*,

*lookup range*, {2,3,...,n}, FALSE))

As you see, we use an array constant in the third argument to perform several lookups within the same VLOOKUP formula in order to get the sum of values in columns 2,3 and 4.

And now, let's adjust this combination of VLOOKUP and SUM functions for our data to find the total of sales in columns B - M in the above table:

`=SUM(VLOOKUP(B2, 'Monthly sales'! $A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))`

**Important!** Since you are building an array formula, be sure to hit Ctrl + Shift + Enter instead of a simple Enter keystroke when you finished typing. When you do this, Microsoft Excel encloses your formula in curly braces like this:

`{=SUM(VLOOKUP(B2, 'Monthly sales'!$A$2:$M$9, {2,3,4,5,6,7,8,9,10,11,12,13}, FALSE))}`

If you press the *Enter* key as usual, only the first value in the array will get processed, which will produce incorrect results.

Tip. You may be curious why the formula displays [@Product] as the lookup value in the screenshot above. This is because I converted my data to table (*Insert* tab > *Table*). I find it very convenient to work with fully-functional Excel tables and their structured references. For example, when you type a formula into one cell, Excel automatically copies it across the entire column and in this way saves you a few precious seconds :)

As you see, using the VLOOKUP and SUM functions in Excel is easy. However, this is not the ideal solution, especially if you are working with big tables. The point is that using array formulas may adversely affect the workbook's performance since each value in the array makes a separate call of the VLOOKUP function. So, the more values you have in the array and the more array formulas you have in your workbook, the slower Excel works.

You can bypass this problem by using a combination of the INDEX and MATCH functions instead of SUM and VLOOKUP, and I will show you a few formula examples in the next article.

Download this VLOOKUP and SUM sample

## How to perform other calculations with Excel VLOOKUP function

A moment ago we discussed an example of how you can extract values from several columns in the lookup table and calculate the sum of those values. In the same fashion, you can perform other mathematical calculations with the results returned by the VLOOKUP function. Here are a few formula examples:

Operation | Formula example | Description |
---|---|---|

Calculate average | {=AVERAGE(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} | The formula searches for the value of cell A2 in 'Lookup table' and calculates the average of values in columns B,C and D in the same row. |

Find maximum value | {=MAX(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} | The formula searches for the value of cell A2 in 'Lookup table' and finds the max value in columns B,C and D in the same row. |

Find minimum value | {=MIN(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} | The formula searches for the value of cell A2 in 'Lookup table' and finds the min value in columns B,C and D in the same row. |

Calculate % of sum | {=0.3*SUM(VLOOKUP(A2, 'Lookup Table'$A$2:$D$10, {2,3,4}, FALSE))} | The formula searches for the value of cell A2 in 'Lookup table', sums values in columns B,C and D in the same row, and then calculates 30% of the sum. |

Note. Since all of the above formulas are array formulas, remember to press Ctrl+Shift+Enter to enter them correctly in a cell.

If we add the above formulas to the 'Summary Sales' table from the previous example, the result will look similar to this:

Download this VLOOKUP calculations sample

## LOOKUP AND SUM - look up in array and sum matching values

In case your lookup parameter is an array rather than a single value, the VLOOKUP function is of no avail because it cannot look up in data arrays. In this case, you can use Excel's LOOKUP function that is analogues to VLOOKUP but works with arrays as well as with individual values.

Let's consider the following example, so that you can better understand what I'm talking about. Suppose, you have a table that lists customer names, purchased products and quantity (*Main table*). You also have a second table containing the product prices (*Lookup table*). Your task is to make a formula that finds the total of all orders made by a given customer.

As you remember, you cannot utilize the Excel VLOOKUP function since you have multiple instances of the lookup value (array of data). Instead, you use a combination of SUM and LOOKUP functions like this:

`=SUM(LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)*$D$2:$D$10*($B$2:$B$10=$G$1))`

Since this is an array formula, remember to press Ctrl + Shift + Enter to complete it.

And now, let's analyses the formula's ingredients so that you understand how each of the functions works and can to tweak it for your own data.

We'll put aside the SUM function for a while, because its purpose is obvious, and focus on the 3 components that are multiplied:

`LOOKUP($C$2:$C$10,'Lookup table'!$A$2:$A$16,'Lookup table'!$B$2:$B$16)`

This LOOKUP function looks up the goods listed in column C in the main table, and returns the corresponding price from column B in the lookup table.

`$D$2:$D$10`

This component returns quantity of each product purchased by each customer, which is listed in column D in the main table. Multiplied by the price, which is returned by the LOOKUP function above, it gives you the cost of each purchased product.

`$B$2:$B$10=$G$1`

This formula compares the customers' names in column B with the name in cell G1. If a match is found, it returns "1", otherwise "0". You use it simply to "cut off" customers' names other than the name in cell G1, since all of us know that any number multiplied by zero is zero.

Because our formula is an array formula it iterates the process described above for each value in the lookup array. And finally, the SUM function sums the products of all multiplications. Nothing difficult at all, it is?

Note. For the LOOKUP formula to work correctly you need to sort the lookup column in your Lookup table in ascending order (from A to Z). If sorting is not acceptable on your data, check out an awesome SUM / TRANSPOSE formula suggested by Leo.

Download this LOOKUP and SUM sample

## VLOOKUP and SUMIF - look up & sum values with criteria

Excel's SUMIF function is similar to SUM we've just discussed in the way that it also sums values. The difference is that the SUMIF function sums only those values that meet the criteria you specify. For example, the simplest SUMIF formula `=SUMIF(A2:A10,">10")`

adds the values in cells A2 to A10 that are larger than 10.

This is very easy, right? And now let's consider a bit more complex scenario. Suppose you have a table that lists the sales persons' names and ID numbers (*Lookup_table*). You have another table that contains the same IDs and associated sales figures (*Main_table*). Your task is to find the total of sales made by a given person by their ID. At that, there are 2 complicating factors:

- The mail table contains multiple entries for the same ID in a random order.
- You cannot add the "Sales person names" column to the main table.

And now, let's make a formula that, firstly, finds all sales made by a given person, and secondly, sums the found values.

Before we start on the formula, let me remind you the syntax of the SUMIF function:

`range`

- this parameter is self-explanatory, simply a range of cells that you want to evaluate by the specified criteria.`criteria`

- the condition that tells the formula what values to sum. It can be supplied in the form of a number, cell reference, expression, or another Excel function.`sum_range`

- this parameter is optional, but very important to us. It defines the range where the corresponding cells' values shall be added. If omitted, Excel sums the values of cells that are specified in the range argument (1^{st}parameter).

Keeping the above info in mind, let's define the 3 parameters for our SUMIF function. As you remember, we want to sum all the sales made by a given person whose name is entered in cell F2 in the main table (please see the image above).

**Range**- since we are searching by sales person ID, the*range*parameter for our SUMIF function is column B in the main table. So, you can enter the range B:B, or if you convert you data to a table, you can use the column's name instead:`Main_table[ID]`

**Criteria**- because we have sales persons' names in another table (lookup table), we have to use the VLOOKUP formula to find the ID corresponding to a given person. The person's name is written in cell F2 in the main table, so we look it up using this formula:`VLOOKUP($F$2,Lookup_table,2,FALSE)`

Of course, you could enter the name in the lookup criteria of your VLOOKUP function, but using an absolute cell reference is a better approach because this creates a universal formula that works for any name input in a given cell.

**Sum range**- this is the easiest part. Since our sales numbers are in column C named "Sales", we simply put`Main_table[Sales]`

.Now, all you need is to assemble the formula's parts and your SUMIF + VLOOKUP formula is ready:

`=SUMIF(Main_table[ID], VLOOKUP($F$2, Lookup_table, 2, FALSE), Main_table[Sales])`

Download this VLOOKUP and SUMIF sample

## Formula-free way to do vlookup in Excel

Finally, let me introduce you to the tool that can look up, match and merge your tables without any functions or formulas. The Merge Tables tool included with our Ultimate Suite for Excel was designed and develop as a time-saving and easy-to-use alternative to Excel's VLOOKUP and LOOKUP functions, and it can be very helpful both to beginners and advanced users.

Instead of figuring out formulas, you simply specify your main and lookup tables, define a common column or columns, and tell the wizard what data you want to fetch.

Then you allow the wizard a few seconds to look up, match and deliver you the results. If you think this add-in may prove helpful in your work, you are most welcome to download a trial version by using the below link.

## Available downloads

VLOOKUP with SUM and SUMIF - formula examples (.xlsx file)

Ultimate Suite - trial version (.exe file)

## 423 comments

How to Find Over all Total Value in Single Name - Vlookup

Hi! Have you tried any of the methods described in this blog post? If they don't work for you, please describe your problem in detail and I'll have a look at a possible solution.

Hi! I am gathering scores from about 100 football matches. There are 5 columns. Date, Home, Score 1, Away, Score 2. Some teams played multiple matches and I want to get the total accumulated scores of all teams that played.

Hi! Since your columns do not include the team name, your question is not entirely clear. On the basis of your information, I think you can calculate the sum scores by condition with the help of the SUMIFS function. If this does not help, explain the problem in detail.

There is a team name, on the 2nd and 3rd column.

=SUM(VLOOKUP(H3,$B$3:$E$14, 2, FALSE))

I actually used this formula but for some reason, it only shows the score on the first game and not the accumulated scores of different matches.

Hi! The VLOOKUP function will only return the first result that it finds. So use the SUMIFS function from my earlier recommendation.

For example:

=SUMIF(B3:B10,H3,C3:C10)

You can also use this tutorial: Excel SUMPRODUCT function with formula examples. The formula might look something like the following:

=SUMPRODUCT((B3:B10=H3)*C3:C10)

Actually .i have a concern that telecom company there is so many account number,

Account Number Amount

1.324567777 300

1.324567777 300

1.345677890 -40

1.345677890 20 I want to want the account duplicate and same time i want to get the total amount in one account in one column by using formulae ,can you please me regarding this concern using sumif

Like this format

Account Number Amount

1.324567777 600

1.324567777 0

1.345677890 20

1.345677890 0

Thankyou

Chithra

Hi! You can learn more about sum by condition in this article: How to use SUMIF function in Excel with formula examples. For example:

=SUMIF(A1:A100,A1,B1:B100)

To get the amount for each Account Number, you can also use a Pivot Table.

Thanks for the info,

can you help me, i have a question. for a big data how do i make a dynamic refereance for {2,3,4,5,6,7,8,9,10,11,12,13}.

created a manual serie. {8;9;10;11;12;13;14;15;16;17;18;19;20;21;22;23;24;25;26;27;28;29;30;31;32;33;34;35;36;37;38;39;40;41;42;43;44;45;46;47;48;49;50;51;52;53;54;55;56;57;58;59}

but it is so hard to keep the formula updated all the weeks

ıt should start with H (8th) colulmn and finish at the end of the year.

Hi! What do you want to calculate exactly? What formula you used? Your question is not entirely clear, please specify.

Hello, I have 3 columns

Name ID Amounts I want to get sum of amounts in a cell where I write Names , some rows might be exactly same in the worksheet

AA 22 200

AA 22 200

AA 33 100

BB 11 300

so when I type AA in a cell, I want to get 100+200=300 value not 200+200+100=500 , so I want excel to sum only different amounts of the same name

I have cell where names are written for example AA and I want to put sum of amounts next to The Name cell

P.S

My worksheet needs to have duplicate rows, I can't delete them

And I don't have UNIQUE function on my excel

Hi! In your version of Excel, I recommend copying the data to a separate sheet and removing duplicate rows. You can use the Duplicate Remover tool to remove duplicate rows. You can then use the COUNTIFS function to calculate count by condition.

You can also find useful information in this article: How to remove duplicates in Excel (duplicate rows, values and partial matches).

yes, but I need to have duplicates

and I want SUM of amounts not Count

You remove duplicates from the copy of your data. To find the sum by condition, use the SUMIFS function.

Duplicate remover tool is available as a part of our Ultimate Suite for Excel that you can install in a trial mode and check how it works for free.

I have a delivery record for 10 years for more than 1000 items.

I need to know each item last delivery year. What is the formula that I can use to identify each item last delivery year?

Hi! You can get the records for the desired year with the FILTER function. Then use the UNIQUE function to get a list of items without duplicates. You can find the examples and detailed instructions here: Get a list of unique values based on criteria.

im trying to apply this to my sheet, but am getting stuck, i have unique ids instead of names, the ids are in 2 seperate tables, i need to subtract table 1 from table 2 based on id number..

could this be done using this method or am i looking for another way?

table 1

id, games lost, total value

1 5 100

2 12 1500

3 3 1200

table 2

id, games won, total value

1 7 1100

2 1 2500

3 9 800

table 3

id, total games played, total value

1 7

2 13

3 12

table 3 will be the total profit/ loss

if this make sense

Hi! If I understand your task correctly, you are trying to use the SUMIFS function to sum values by condition:

=SUMIFS(Sheet2!C1:C10,Sheet2!A1:A10,Sheet3!A1) - SUMIFS(Sheet1!C1:C10,Sheet1!A1:A10,Sheet3!A1)

For more information, please visit: How to use Excel SUMIFS and SUMIF with multiple criteria.

Hello,

I have an excel sheet with client names in Column A and the corresponding revenue for the last 24 months. I want to organise the the customers based on the highest revenue for each month. How can i do this in excel.

Hi! Based on your description, it is hard to completely understand your task. If you want to sort customers, use the SORT function as described in this manual: Excel SORT function - auto sort data using formula. You can also use these guidelines: How to alphabetize in Excel: sort columns and rows A-Z or Z-A. If this does not help, explain the problem in detail.

Hi Alex,

I have a list of projects (column a) and man hours (column b). I want to total the man hours for the projects that contain “PI17” anywhere in their project name.

What would you suggest?

Thank you

Hi! Identify the cells that contain the text you want. For more information, please read: How to find substring in Excel. Then use the SUMPRODUCT function to sum the numbers corresponding to the selected cells. For example:

=SUMPRODUCT(--(ISNUMBER(SEARCH("PI17",A1:A10))),B1:B10)

Thanks for your help. That worked great!

I have an issue, I have a data validation list (Product Name) in sheet A, in sheet B i have the same data but in a simple list but with a second value in column B (Commission Rate that applies to Product) . I want to return the value in column B based on what I select in drop down on Sheet A

Hi! You can search for the Product Name, that is selected in the drop-down list, in the list on worksheet B. To extract the corresponding Commission Rate value from column B on worksheet B, use the VLOOKUP formula as described in this manual: How to Vlookup from another sheet in Excel. I hope I answered your question. If something is still unclear, please feel free to ask.

01-Aug-23 Ahmed RK 10000

01-Aug-23 Akash RK 20000

01-Aug-23 Ahmed KH 30000

01-Aug-23 Akash KH 40000

02-Aug-23 Ahmed RK 50000

02-Aug-23 Akash RK 60000

02-Aug-23 Ahmed KH 70000

02-Aug-23 Akash KH 80000

Dear Friend

i need google sheets SUM formula of

01-Aug-23 RK 30000 (SUM : 10000+20000)

01-Aug-23 KH 70000

02-Aug-23 RK 110000

Hi.

Although I can use the VLOOK function OK, I do have a problem I cant resolve.

So, VLOOKUP returns a number (In this case an amount of money)

I want to multiply this with another column value that is a qunatity to get a total

All I get back is #NAME?

Thanks - in advance.

Andrew

SIGH ... ignore this question. Blame it on a senor moment...!

Hi. I can't guess which formula you are using. However, you are incorrectly writing the name of the Excel function. This is indicated by the error #NAME?

Hi

please help me

I have two columns which are headers Month & Revenue

line January, February, March etc and the second col 1000, 2000, 3000

I need a formula to add upto the month i.e. when i need upto february sum of above month January and February (1000+2000=3000)

and similarly march shoud results 1000+2000+3000

thanks in advance

Hello!

If I understand your task correctly, you can also find useful information in this article: How to do a running total in Excel (Cumulative Sum formula). If this does not help, explain the problem in detail.

Hi there I wonder if you can help me with a single question I have for a book of really simple data but the requirement is killing me

Sheet 1 col A= list of kids' names, col B=their birth date and col C=their age (calculated using DATEDIF so it stays updated)

Sheet 2 col A= list of days (eg "FRI AM" "MON PM", col B=the name of each kid attending a nursery on that day

- of course, each day and each kid appears more than once, but never more than once together (a kid can't attend more than once for the same session right?)

Sheet 3 has cols for each of the days, and each of those cols is a list of kids that attend on that day, extracted from Sheet 2, by matching the title of the column (eg "MON AM") with the each instance of it in rows in Sheet 2 and reading the list of names found against a matching day name [ =IFERROR(INDEX(SESSIONS!$B$1:$B$300, MATCH(0, COUNTIF(A$7:A7, IF(SESSIONS!$A$1:$A$300=A$7,SESSIONS!$B$1:$B$300,A$7)), 0)),"") ]

So what I need, is a total of each age range attending on each day.

I can share the sheet if you like.

Thanks for all you do.

Hello!

To count the number of values by condition, I recommend using the SUMPRODUCT function. Look for the example formulas here: Conditionally count cells with multiple criteria.

I hope this will help, otherwise don't hesitate to ask.

Thanks a lot. That function is what I was looking for. I was fighting with long formulas which do the same but this is much easier.

I know this thread is old but just shooting it out there... I have a multi sheet spread sheet keeping track of job hours. I have used VLOOKUP in succession to sum all the hours on multiple sheets and it works great... Until it gets to a sheet that does not contain the lookup value. I have searched all over for my issue, and VLOOKUP may be the incorrect solution. I was wondering if I could rattle anyone's brain to make this work. I.E. I have 1 excel document with 52 tabs. Each tab is a work week starting from January so WW1 is all the hours on sed jobs I did for that week. "joes house 2 hours ; mikes house 3 hours" etc... WW2, WW3 etc... Until WW52. This is the function I made to add hours together... =SUM(VLOOKUP(O30,'WW29'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW30'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW31'!$A$7:$M$110,{13},FALSE)) And it works great. But when that job is finished it is not on (for example WW32 tab). Hence I get the #N/A error. so for example, as the previous one works great when I expand the formula to cover all 52 sheets... =SUM(VLOOKUP(O30,'WW29'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW30'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW31'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW32'!$A$7:$M$110,{13},FALSE)) I get the #N/A error because the job is not listed on WW32. But I may add hours to that on WW45. Is there a way to make VLOOKUP skip a sheet that does not have the referenced value and continue summing it till the end? I apologize, this may be as clear as mud but I will clarify anything if need be...

Hello!

The answer to your question can be found in this article: Excel IFERROR & VLOOKUP - trap #N/A and other errors.

I hope it’ll be helpful.

Alex, thank you,

I have read that article before, but it does not seem to cover the summing part of my equation. you can set iferror to return text or even blanks, but the article does not cover summing. I'm looking for how to SUM multiple sheets when some of the sheets do not contain the lookup value. Instead of #N/A it just returns "you've ENTERERED TOO MANY ARGUMENTS FOR THIS FUNCTION"...

=IFERROR(VLOOKUP(O30,'WW29'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW30'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW31'!$A$7:$M$110,{13},FALSE),VLOOKUP(O30,'WW32'!$A$7:$M$110,{13},FALSE),"")

And that's just 3 sheets

Hello!

If you want to sum values, use the SUM function. Apply the IFERROR function to each VLOOKUP. In the value_if_error argument, use not the empty value "", but 0.

For example,

=SUM(IFERROR(VLOOKUP(O30,'WW29'!$A$7:$M$110,13,FALSE),0), IFERROR(VLOOKUP(O30,'WW30'!$A$7:$M$110,13,FALSE),0))

I hope I answered your question.

You sir, are a genius! I cannot thank you enough! That works perfectly! I really appreciate your time in helping me with this calculation!

I've gone though so many different examples and I cannot find anything that will help me get the result that I'm looking for.

So I have started a spreadsheet that will have multiple worksheets of data for each school year. Within each school year, I've or will create a named range ($A$6:$C$53). Columns and A & B are the two columns that I need for this issue. Column A is the name of the fund, while Column B is the amount of the fund.

In addition to a worksheet for each school year, I've created worksheets for each individual fund that allows me to keep a running history of the funds. For most of the individual funds, I've been able to get away with using VLOOKUP as each fund is a unique name. However, the one I'm having issue with is for the "General Funds" as there are several funds that have that name and I'm trying to add them all together.

On each individual fund's worksheet, the formulas are referencing several different cells A1 is the name of the fund that it is searching for within the named ranges, while A3 and down will be the reference cell on which named range to use.

For example:

=VLOOKUP($A$1, INDIRECT($A3&"_Funds"), 2, FALSE)

$A$1= 'Athletic Funds - The name of the fund to search for

$A3=FY202122 - The named range to search in.

This formula returns the value in Column 2 (Column B) corresponding to the fund name from $A$1 from the named range FY202122_Funds.

However, I cannot get it to sum up the values from Column B when there are multiple funds with the same name. I feel like I've tried nearly everything I can think of...including XLOOKUP, INDIRECT, SUM, SUMIF, SUMPRODUCT, ETC.

Some of the errors that I've received is #VALUE and #SPILL, and even the most lately, I've gotten $0.00. This is the latest formula that I've used...=SUM(LOOKUP($A$1,A3&"_Funds",FALSE)) and have gotten the $0.00 result.

Thank you for your time in advance, as this is getting frustrating.

Hello!

The VLOOKUP function can only look up a value in one range. It can only return one value. I assume that you need to find several values by the name of the fund. Pay attention to this guide: How to do multiple Vlookup in Excel using a formula.

Hope this is what you need.

What if I want to add up cells that are not a numeric value? Like I have a list of names and I want to add all the "Amy" cells up to tell me how many there are?

Hi!

To count cells with text, you can use the SUMPRODUCT function:

=SUMPRODUCT(--ISTEXT(A1:A10))

You can check if a cell contains text using the ISTEXT function.

This should solve your task.

Hi,

sumproduct works great if want to sum all values

But I want to sum only positive or only negative value

eg.

A 1

B 2

C -5

A 2

C 6

B 4

B -3

now I want

positive sum of A (Result = 3)

negative sum of A (Result = 0)

positive sum of B (Result = 6)

negative sum of B (Result = -3)

Thanks

Hello!

Here is the article that may be helpful to you: Excel SUMIFS and SUMIF with multiple criteria.

For example:

=SUMIFS(B1:B7,B1:B7,">0",A1:A7,"A")

I need one Calculation Formula...

I have accounting Ledger in Excel Format for Home Accounting Only with Pivot Table.

In summary I need the sum of amount of money debited from the accounts of Various Bank accounts...

Following is the Ledger I created...

Where I need to know the amount of money debited and Credited from Cash, SB Account, Airtel App, GPay, IRCTC App from the below Pivot Table. The value should be under the Credit and Debit Row seperately.

MY HOME ACCOUNTS

SL No Particulars Credit Debit Balance

1 CASH ₹ 7,195.00 ₹ 7,195.00

2 SB Account ₹ 74,261.22 ₹ 74,261.22

3 Airtel App ₹ 500.00 ₹ 500.00

4 Gpay ₹ 498.00 ₹ 498.00

5 IRCTC App ₹ 0.00 ₹ 0.00

TOTAL ₹ 82,454.22 ₹ 0.00 ₹ 82,454.22

Sl No Day Date Particulars Credit Transaction Amount

1 Tuesday 01-03-2022 Current Balance Credit SBI Account ₹ 849.22

2 Tuesday 01-03-2022 Current Balance Credit CASH ₹ 40.00

3 Tuesday 01-03-2022 Current Balance Credit SBI Account ₹ 1,000.00

4 Tuesday 01-03-2022 Salary Credit SBI Account ₹ 56,270.00

5 Tuesday 01-03-2022 Offerings Debit CASH ₹ 5,627.00

6 Tuesday 01-03-2022 House Rent Credit SBI Account ₹ 3,600.00

7 Tuesday 01-03-2022 Vegetable Debit CASH ₹ 138.00

8 Tuesday 01-03-2022 Groceries Debit SBI Account ₹ 742.00

9 Tuesday 01-03-2022 Groceries Debit Gpay ₹ 278.00

10 Tuesday 01-03-2022 Others Credit Gpay ₹ 220.00

11 Tuesday 01-03-2022 Fuels Credit Airtel App ₹ 500.00

12 Tuesday 01-03-2022 Vegetable Debit CASH ₹ 10.00

13 Tuesday 01-03-2022 Car Debit CASH ₹ 100.00

14 Tuesday 01-03-2022 Fruits Debit CASH ₹ 70.00

15 Wednesday 02-03-2022 Salary Credit SBI Account ₹ 11,800.00

16 Wednesday 02-03-2022 Clothes Debit CASH ₹ 270.00

17 Wednesday 02-03-2022 Bike Debit CASH ₹ 440.00

18 Wednesday 02-03-2022 Others Debit CASH ₹ 360.00

19 Wednesday 02-03-2022 Foods Debit CASH ₹ 140.00

Please Assist me...

Thank You...

Hello!

If you are using a PivotTable, I recommend that you read this guide: How to use pivot tables in Excel

I think that with the help of a pivot table you can solve all your problems.

I have a problem very similar to the vlookup with sumif, but slightly different:

given a table like:

A 110

B 120

C 130

D 150

E 160

F 170

G 180

H 200

I 5000

and a lookup table:

A 1

B 1

C 1

D 3

E 3

F 3

G 3

H 4

I 4

J 4

K 5

in a single cell I want to add up the numbers from the first table, where they lookup to 2 (for example) from the second table, is this possible? (without adding helper columns)

Would appreciate any advice

Dominic

Hi!

Your task is not completely clear to me. What result do you want to get exactly?

Yea my example didn't make sense, in a single cell I want to add up the numbers from the first table, where they lookup to 3 (without adding helper columns)

so given 3 we lookup in the second table D, E, F ang G as these lookup to 3 so in a single cell we would sum :

D 150

E 160

F 170

G 180

giving a result of 660

Hello!

If table 1 is in columns A and B and table 2 is in columns C and D, then you can use the formula

=SUM(INDEX(E1:E9,MATCH(FILTER(A1:A11,B1:B11=3),D1:D9,0)))

I hope my advice will help you solve your task.

Thank you Alexander, but I think my excel might be old as I get "That function is not valid" for FILTER function? I am using Microsoft Excel Professional Plus 2016, not office 365.. Thanks

Hello!

If you use the guide How to VLOOKUP multiple values in Excel, try the following formula:

=SUM(IFERROR(INDEX(E1:E9,MATCH(IFERROR(INDEX($A$1:$A$11, SMALL(IF(3=$B$1:$B$11, ROW($B$1:$B$11)-0,""), ROW(B1:B11))),""),$D$1:$D$9,0)),""))

thanks again, this is very close but only works for me if I put the array into multiple cells, in a single cell it only sums the first value found, I will look at the link you provided, thank you for trying

Im trying to find and sum a specific phrase corresponding with numbers. 2 separate columns - one with the phrases and one with numbers.

Then if possible to add them all up in entire workbook.

Hi!

The information you provided is insufficient to understand the problem.

Thank you! It works!

Hello,

I'm trying to create a formula on sheet 2 that pulls data from Sheet 1.

Column F has priority numbers 1-3. and column V either has a blank or a date in the cell.

I need to create a formula to sum up how many of the Priority 1's in (column F) have a date (in column V).

Hello!

To count the number of values by condition, use the COUNTIFS function.

Cell :

A1 alpha B1 1000

A2 beta B2 2000

A3 gamma B3 3000

A4 alpha B4 4000

A5 beta B5 5000

A6 gamma B6 6000

I need the function to find all the alpha and sum their corresponding numbers

Hi!

You can find the examples and detailed instructions here: How to use SUMIF function in Excel to conditionally sum cells.

I am trying to pull the sum of multiple cells in the same column that have the same part# but different qty's.

part A 6

Part B Qty 2

part C Qty 1

part A Qty 4

part A Qty 9

Part C Qty 20

Hello!

Use the SUMIF function. You can find examples and detailed instructions here.

Hello!

I have a sheet that has some company names in column V and a running tally in column M. There are cases where the same company name comes up more than once, in which case I want to either SUM / find the MAX value of all the cells in column M that correspond to that company name. My goal is to count the number of companies that have a zero on the running tally across all instances of that company name in the sheet, so I need to exclude any that have a 1 or more in the running tally across all instances of the company name (hence the SUM / MAX). Unfortunately, I cannot edit the layout of the sheet and my final count has to be in a single cell (I cannot generate other tables / reference worksheets), and I have been unable to figure out how to make this work. Any help or insight you could provide would be greatly appreciated!

Hello!

Unfortunately, you cannot solve your problem using Excel functions in one cell.

I'm really sorry, we cannot help you with this issue.

Sir,

Please how can I apply LOOKUP formulae to Sum total sales of a particular sales person from a large data table of sales representatives all combined together in a sheet and compute my result in the sheet 2.

Thank you

Hi, I want to sum all non-matching values in a vlookup.

As an example, I have a list of one hundred locations. I have a table of one thousand locations and their population numbers. I can find the population of the matching one hundred locations, now I want the sum of the remaining 900.

Thanks for any assistance.