# Summing Data In A Table Using Row And Column Criteria (with Multiple Instances)

May 14, 2013

I have a table that looks like the following, only it's actually much larger:

_Red Blue Green Blue
A 2 4 2 3
B 5 2 1 1
C 3 1 2 5
D 2 3 4 2

As an example, I'm trying to sum all cells that match Blue and C. The answer should be 6, but I always end up with either zero or #VALUE.

## Summing Data In Multiple Columns Using Both Row And Column Criteria

Mar 6, 2013

I have been trying multiple SUMIFS and SUMPRODUCT formulas to try and revamp my budget spreadsheet. My first criteria is the month being a certain number (contained in a column), if that condition is met it needs to look for the account number I am budgeting for (row heading) and if it matches both criteria I need it to sum the Column that has the row heading of the account number. I have succeeded in doing a SUMIFS and manually finding the column that has the heading of the account number I am looking for, but I would like this to be an automated process so I can copy the formula. Below is the data I'm using (a very, very small portion of it), and the results are how my budget sheet is going to look. These will be on separate tabs in the workbook.

EX: For July electric revenue (44211) I need my formula to: (if the Month column=7 & if the Account Code row heading=44211, sum the column that has the heading of 44211). So far I cannot get this to work.

DATA
Account Code
44211
44215

[Code]....

## Excel 2007 :: Summing Column On Data Sheet Based On Multiple Criteria

Feb 23, 2012

Excel 2007. I have an Excel file that contains a data dump from an external database file with numerous analytical sheets that perform calculations. Some of these calculations utilize the SUMIFs function that was introduced in Excel 2007. This function does an outstanding job of summing a column on the data sheet based on multiple criteria.

However, someone high up in management in my organization would like to "drill down" into the data behind the

SUMIFs formulas to get a quick snapshot of the lines in the database that roll into the
SUMIFs formula. =SUMIFS(DataBase!E:E,Data!A:A,C7,DataBase!B:B,D7,DataBase!C:C,E7,DataBase!D:D,F7)

If I double click on a cell with the formula above, Excel takes me to the Database tab and selects Column E which is close, but not exactly what I need. What I really need is for Excel to only show the rows on the database sheet that make up the total in the SUMIFs formula and not the entire data dump from the database.

At present, we have to manually apply the autofilter on multiple columns to show the rows in column E that make up the total in the SUMIFs formula which is a tedious and time consuming task. Is there a way to force Excel to do this? Suggested custom database application or pivot tables, but we do not want to reinvent the wheel.

## Summing Multiple Instances Of The Same Name

Apr 17, 2009

Using Excel2007 / Vista

Weekly I receive commission payment data. Essentially 2 columns, Col A the client name, Col B the commission amount. I like to sort the data so that I have a total commission amount for each client
Firstly I sort the 2 Cols alphabetically. Where there is only one instance of a client name, the total commission is obvious.
But any client can appear several times (anything up to 6) with corresponding commission amounts for each name occurrence.
One week a client may not appear at all, or they may appear from 1 to 6 times.
I seek a formula which, where there are multiple instances of the same client name. will total all the commission payments for that client. Naturally the formula can be in a new Col.

## Summing Data From Multiple Workbooks Based On Multiple Criteria

May 14, 2014

I track distribution using 1 workbook for each sales rep. Each workbook has a separate worksheet (scorecard) for each of their locations showing which of our products are used in that location, for various functions. Each of the locations has a segment (bar, nightclub, casual dining, etc.), and a class (AA, A, B, etc.). All of the scorecards are identical, and are in the same file folder on our shared drive.

Sample:

Location A Class:AA Segment: Sports Bar Rep: John Smith

Product: Product X Product Y Product Z
Well: 1 0 0
Back Bar: 0 1 0

I was unable to find an HTML maker so this is not an actual sample but what I'm looking at. The above data indicates that for location A (Sports Bar, Class AA), Product X is used as the well, Product Y is on their back bar and their cocktail menu, and Product Z is not used at this location at all.

I need to set up an analytic workbook where I can sum data based on multiple and grouped criteria, and for multiple reps ie- display a scorecard that shows totals for nightclubs and sports bars, class AA, A, and B, for a particular rep; OR display a scorecard that shows totals for nightclubs, class A for a group of reps. Ideally I would like to do this using drop down menus that allow the user to select multiple criteria in each of the drop downs, having the data auto-populate based on those selections.

## Summing Data Based On Multiple Criteria

Sep 22, 2009

I have a table of data: - In cell A1: to C7
A B C#Group Aug-091Merchant Bar2002Plate3Structural - HR1004RHS5Plate3006Structural - HR300

What I would like is a formula that sums up column C if Column B of that row is "Plate" for example

## Count Number Of Instances Of Dates By Month With Multiple Criteria

Feb 5, 2008

I have attached a small copy of the worksheet.

I need to count when the following set of 3 criteria criteria are met:

1: The Assessor Initials = DS

2: the month = Mar

3: Check 4 Ranges = columns I, K, M, O

I've tried using sumproduct (sucessful for other data) but not able to get it to work with dates.

## Summing Numbers With Multiple Criteria

Aug 27, 2009

Column A: Start Date
Column B: End Date
Column C: Ethnicity Code (letter and number such as B1)
Column D: Hours

What I need to do is work out how many hours each ethnic group did in a specific month.

## Summing Up A Criteria Across Multiple Tabs

Apr 23, 2009

I'm trying to sum a criteria of all M's in one column that are x's in a different column, throughout multiple worksheets.

I'm able to get the summary number for 1 worksheet using the below formula (*W1 is the worksheet name); however, how do i encapsulate all the worksheets (lets say W1 through W10), please note that some of the worksheets have different ranges (meaning, not all are from Row 2 to 6)

=SUMPRODUCT(--(INDIRECT("'W1'!C2:C6")="M"),--(INDIRECT("'W2'!D2:D6")="x"))

I tried to replace W1 with W1:W10.

## Summing Up A Specific Criteria In Multiple Tabs

Nov 18, 2008

I am trying to review a cell range for a specific criteria, and then sum up another cell range if the criteria matches. Here are the formulas I have typed in - there are two columns I am trying to calculate using the same formula, they are next to each other:

=SUMIF('MASTER POINT SCHEDULE'!I2:I841,"0ACA101",'MASTER POINT SCHEDULE'!O2:O841)

=SUMIF('MASTER POINT SCHEDULE'!J2:J841,"0ACA101",'MASTER POINT SCHEDULE'!P2:P841)

## Populating Table With Data Using Multiple Criteria?

May 6, 2013

I am attempting to populate multiple rows in one column with data from another table. I need to get the correct street address using multiple values, i.e. first name, last name and city, as some of the names double up.

Is there a way to do this? I have pasted below an example of what I need done as reference.

Last Name
First Name

[Code]....

## Extracting Data From A Table - Multiple Criteria

Jul 31, 2008

I have a data table that has the following structure

Team manager Warrington 10 12 14 16
Team manager Liverpool 8 10 11 12
Commercial Manager Warrington 25 28 32 33

I need to extract the data out into a flexbible summary table where i can choose the job title, location and year.

I can use the following index / match formula to extract the salary that matches the job title and location.

(INDEX('Emp In'!\$c\$5:\$c\$130,MATCH(1,('Emp In'!\$A\$5:\$A\$130=\$B3)*('Emp In'!\$b\$5:\$b\$130=\$C3),0) - Where b3 = job title, C3 = location, Column C = Salaries year 1

However I cannot add the flexibility to choose a year as this formula fixes the index on a chosen column (yr 1 column C is this instance)

## Summing With Date Criteria In A Column

Jan 22, 2010

I would like to sum G column (in my sample) that will meet a date criteria in C (like from 10/9/2009 to 11/9/2009) or similar. I tried it already with a formula =IF(c2=DATEVALUE("10/9/2009")=<("11/9/2009"),G2,0) could not make it work but its okay without the =<, or a single entry date (a sample in AB column), with this formula its being transferred to another column and being summed, what am trying to figure out is to make a formula or function that it can be incorporated in a sum at the bottom of G column if possible, I would be able to check how much cash collected in a week, days or month....

## User Defined Function For Summing Multiple Criteria

Aug 5, 2008

I am in need of creating a User Defined Function in Excel that will sum numeric values based on two text criteria. I have a large set of data in an Excel worksheet that includes a column for " Market Type" and a column for "Location ID". I would like to develop a User Defined Function that allows a new or inexperienced user of the template to use the User Defined Function to select first a "Market Type" then select a "Location ID" and get the sum of the amount those values represent. A SUMPRODUCT function in Excel works for this, but can be awkward or intimidating for a new or inexperienced user. For that reason, I am specifically looking for a User Defined Function that will simply the formula for them while at the same time maintaining the degree of accuracy I need.

## Summing Multiple Table Values Using VLookup

May 20, 2013

I'm trying to get a more manageable formula for totaling multiple values in one box using Vlookup. For clarification this is what I mean:

On spreadsheet A, I have this table:
[IMG][/IMG]

The value in B2 is the name of the item I am making and the items below are the components required to manufacture said item. In order to build them, I need so many (Column E) and I have indexed my remaining values simply by doing =E3-H3. To build each component requires minerals which are listed on a seperate sheet (Sheet B) as such:

[IMG][/IMG]

The only Value in the above table I am truly interested in is "Current" (E Column).

Back on SheetA, I have a second table which adds all of the component minerals up for a grand total of the minerals required to make the item in B2 as such:
[IMG][/IMG]

Now, this is my issue: The code for N3 is incredibly unwieldy:

=IF(\$F\$3>0, \$F\$3*VLOOKUP(\$L3,
'Component Materials'!\$A\$15:\$E\$21,5,FALSE))+IF(\$F\$4>0, \$F\$4*VLOOKUP(\$L3,
'Component Materials'!\$A\$25:\$E\$31,5,FALSE))+IF(\$F\$5>0, \$F\$5*VLOOKUP(\$L3,
'Component Materials'!\$A\$45:\$E\$51,5,FALSE))+IF(\$F\$6>0, \$F\$6*VLOOKUP(\$L3,
'Component Materials'!\$A\$55:\$E\$61,5,FALSE))+IF(\$F\$7>0, \$F\$7*VLOOKUP(\$L3,
'Component Materials'!\$A\$65:\$E\$71,5,FALSE))+IF(\$F\$8>0, \$F\$8*VLOOKUP(\$L3,
'Component Materials'!\$A\$115:\$E\$121,5,FALSE))+ IF(\$F\$9>0, \$F\$9*VLOOKUP(\$L3,
'Component Materials'!\$A\$135:\$E\$141,5,FALSE))

I set this up to look at a specific value and if it was >0, it would multiply that value times whatever value it found on SheetB ("Component Materials"). The size of this formula makes this difficult to migrate to further items, so I would like to reduce the complexity of the formula.

## How To Arrange Data Range Into New Table Based On Multiple Criteria

Aug 27, 2012

I have a problem whereby I have a list of data that has a date, a transaction and a balance. I want to be able to rearrange this data into multiple tables, one for each month.

The pictures below explain the situation better, on the left is the list of data I need to sort, and on the right is how I'd like the data displayed. So for the May columns I would like to display all transactions that happened in May and, depending on whether it is an income or an expense, the amount in the corresponding column.

i.e. so the finish result looks something like this:

## Summing Up Opposite Numbers In A Column Based On Criteria

Jun 10, 2009

Imagine column A sorted by invoice numbers. Column B has payments applied but due to movement between unapplied cash, some numbers appear as negatives and positives for the same invoice number.

I want to have a formula which says that for each invoice number, look in column b and eliminate opposite numbers and post only unique values. Keep in mind that there may be multiple lines with the same invoice number.

For example, starting with line 5, for invoice 123, payments 400 and -400 in line 5 and 17 and payments 315 and -315 in lines 7 and 9 will zero out but any other unique payment will be picked up and displayed in column C. Lines 5 and/or 17 will be blank in column C.

## How To Get VLOOKUP To Return Multiple Instances Of Data

Jun 23, 2009

Sheet 1 has one column

Customer Number (unique)
e.g.

111
222
333
444

Sheet 2 has two columns

Customer Number

e.g.

111-----billgates@msn.com
111-----billgates@microsoft.com
111-----billgates@hotmail.com
222-----davidbeckham@mufc.co.uk
222-----davidbeckham@fa.co.uk
333-----me@test.com
444-----you@test.com

Now, when I do a vlookup on sheet one to obtain all email addresses for customer 111, it only returns the first email address - e.g. billgates@msn.com

Ideally, I would like;

Is there a function that can achieve this?

If not, can the multiple occurrences be returned in one cell, separated by;?

## Multi Criteria Counting; Count The Number Of Instances That "Project" Appears In Column O AND "TS" In Column N

Nov 21, 2007

Im trying to construct a nested Countif statement. I need to count the number of instances that "Project" appears in Column O AND "TS" in Column N. The range is in another in Sheet2. and the summary in Sheet 1 where I want to have the Countif(AND...??? statement Example Counif(Sheet 1 Column 0 contains "Project" AND if Column N Contains "TS"

## Copy Data Table Seperate Sheets Based On Column Criteria

May 12, 2008

I need to copy all the work sheets into one single work sheet (mastersheet). The source work sheets are having same column structure. The condition which i need to take care of is that after column 3 if at all there is any data till column 10 then in the destination mastersheet these should be copied in different rows with first two columns repeated. I need to do this using VBA macro.

## Summing A Row From A Table Of Data

Oct 27, 2009

The formula is in C4, and I am trying to sum the data when the value of B4 is matched in the table. see attached.

## Summarizing Data To Collect Attributes From Multiple Instances

Oct 27, 2013

My company would receive RFQs from potential customers inquiring the availability of different products, and we are trying to summarise these requests to find a trend.

Each product has several attributes, such as descriptions, keywords, manufacturer's code, etc. The RFQs are all different, with each request providing some attributes while missing others. The only reliably uniform attribute is the product number.

I am trying to build a database with the maximum amount of details on each requested product, therefore consolidating the different requests. I already put the source data on the same sheet and sorted the product numbers. I also know how many instances there are for each product number. In other words, my data look like this:

Product number (sorted)
Repeat count
Attribute 1
Attribute 2

111a
2
aaaa

[Code] .........

Note:
1. The product numbers and repeat count are reliable
2. All the attributes are valid, i.e. as long as one can fill a blank I would take it
3. If none of the instances provide a certain attribute, it is acceptable for it to be left blank

And I am trying to turn it into this:

Product number
(sorted)
Repeat count
Attribute 1
Attribute 2

111a
2
aaaa
bbbb

123456
1
abcde

100x
3
123456
wxyz

I already spent a whole day trying to do it with MATCH, OFFSET, VLOOKUP etc to no avail. It seems I would need some kind of VBS with loop and array functionalities that are beyond me.

## Return Multiple Instances Of Data Related To Number

Nov 26, 2008

The database is for a Prison, and an obvious item to search for on a userform is Prison number. I have a serach option that generates an array listing in a listbox, selecting that item then populates the userform - no problem.

The problem occurs when an individual has more than once instance that has been recorded, is it possible to have more than one row generated in a listbox that can be selected, I only want indiviudal rows selected. The coding I am using for one of the single instance listbox is etc etc etc has been used to demonstrate that the coding continues to the length of the array requied

Private Sub FindAll()
Dim strFind As String 'what to find
Dim rSearch As Range 'range to search

Dim fndA, fndB, fndC, fndD, fndE, 'etc etc etc' As String

Dim i As Integer
i = 1
Set rSearch = Sheet1.Range("ChargeNo.")
strFind = Me.CbAdjFind.Value 'find Charge No
With rSearch
Set c = .Find(strFind, LookIn:=xlValues)..............

## Expand Table To Include Missing Times From Data And Sort Based On Multiple Criteria

May 6, 2014

I have a set of data (assume 2 columns, one with a long name and the second with a time). The names contain variables that must be used as criteria (a single entry may contain "Blue" and "On"/"Off") and times vary, based on when the Name turns "On" or "Off" [in minutes: 25 (On), 47 (Off), 89 (On), 100 (Off) and 137 (On)]. I need to create another table that automatically inserts values for all times, to include the missing times (0 min thru 24 and 26 thru 46, etc.). EX: If the first entry is "Blue-On" at "25 minutes", the cells from 0 minutes to 24 minutes are each "0" and become a "1" at 25 minutes - the following cells are "1" until 47 minutes (where it is turned off). (1 and 0 represent "On" and "Off", respectively)

How can I created a formula to insert the correct numbers into the correct places? I'm willing to have multiple cells with formulas and simply hide the columns that are doing the calculations.

## Finding Data In Column That Meets Multiple Criteria

Jun 16, 2014

Any quick way to extract data from a table. I need to extract a value from a column that meets criteria from two different columns. I thought I could get this to work with vlookup, but have had no success. Sample data below in table 1 and I would like to get my data into table 2.

elevation
type
percent
weight

5000

5000

5000

5020

[Code] ..........

## Summing Different Cells In Column Across Multiple Worksheets

Mar 9, 2009

I am working on a task analysis involving functions performed by different operators, each function marked by an "X" and totaled at the bottom of each operator column. I have seven sheets, named "Main Engine, Propulsion System, APU and ELEC.", etc. Columns B-F on each sheet are headed "C/M, Engr, Nav, LM, DE" respectively.

I need to add/total the bottom figure in each column (total of number of X's for each column) on a separate, final worksheet. Each of the worksheets contains a different number of functions, therefore the total of X's in each column is listed in a different cell# on each worksheet.

So this is my dilemma as the thread title states: I need to be able to sum/total the amount form different cell numbers (same column) across different worksheets into a cell in a separate, final worksheet.

I have figured out how to do this if the numbers are in the same cell across worksheets, but not varying cells by column.

## Summing Cells In Column Based On Data In Another Column?

Feb 27, 2012

I have a statement from an account (which happens to be the government) in which they list every invoice they are paying and each item on that invoice. But they don't have an invoice total. I'd like a way to add up the item totals for each invoice and put the total in column D. Each invoice could have 1 to 10 different items on it.

A(invoice#) B(Item) C(total) D(invoice total)
111 widget 1 \$5
111 widget 2 \$10
111 widget 3 \$8 XXXXX
222 widget 1 \$5
222 widget 5 \$15 XXXXX
333 widget 2 \$10 XXXXX
444 widget 5 \$15 XXXXX

I had thought an IF formula would be the way to go.

## Summing Data In Multiple Workbooks

Jul 18, 2006

I have a lot of paperwork that needs to be entered into Excel. When I finsih entering the data, there will be many workbooks containing many different worksheets. I know how to summarize each workbooks but is there a way I can add specific columns in each of those summary sheets into a new workbook? I guess the question is can I sum data from different workbooks into a new workbook?

## Copy Column Data From Multiple Worksheets To Single Worksheet Based On Header Criteria

Oct 18, 2013

I have a workbook with many sheets of similar but not identical data. I need to extract columns from each sheet based on 5 header criteria and paste them to a single sheet. Each worksheet contains these 5 criteria.

I've been working with the VBA script I've pasted below. It's grabbing the 5 column criteria that I have in sheet 12, and comparing them to sheet 1 in the workbook, then copying them to sheet 12. This much is good, but I need the script to also return the data from the other worksheets as well. I've tried modifying the script based on other loop functions in other scripts I've found, but I'm not having any luck.

## Summing In One Summary Tab Data From Multiple Tabs?

Jan 14, 2014

I have a summary tab in which I am trying to sum data based on specific variables from 30 other tabs in the worksheet

- In the summary tab, I have months (one year worth) over the top row and consultant names in the first column
- Each other tab represents a project
- I want for consultant 1 in summary tab, to have:
For january, the sum of dollars spent in january in each project (so across all tabs)
Same for february to december.

I have attached an exemple Note that the consultant names will always have the same syntax but will not necessarily be in the same cell in each tab.