Array Of Columns / SUMIF A Particular Column / Depending On A Certain Criteria

Jul 24, 2014

I have a download from an accounting general ledger which has the following:

Column A: Category Description
Column B: Country
Column C: Department
Row 1: Months
Note: Each row contains the last 12 months worth of costs

As the categories/criteria can appear multiple times, and there are thousands of lines, I have been using SUMIFS to calculate totals my required combinations (eg. Travel expense, Germany, Sales department)

Every month, the information refreshes to show the most recent 12 months worth of data. And this means I need to manually update my formulas to correct the month column headings, as everything moves by 1 month.

Would something like a SUMIF with a SUMPRODUCT work? Eg. if current month = July, then it would sum everything from the July column automatically?

I have a dump of about 4000 calls (I have minimised the attachment for size sake) that I need to do certain reports on.

The second sheet (dump) has the data in its raw form from our call reporting tool. The 1st sheet (Report) is a broken down version the powers that be wish to see.

My formula needs to meet certain criteria, as you can see I only have 6 columns on the (Report) sheet where as the dump has lots !

The (report) sheet needs to list the Parent Case ID only if the Summary column has BSR at the beginning, if it meets this criteria then it lists the other relevant columns in the (report) sheet.

I am trying to hide or unhide columns E:BL depending if the the cell value in row 54 contains a value (Note cells in row 54 contain formulas). I have written the following code but get a runtime error on the else statement.

Sub UpdateCashflow() Application. ScreenUpdating = False ActiveSheet. Unprotect Password:="LD" ActiveSheet.Select Dim c As Integer For c = 5 To 64 If Cells(54, c) = "" Then ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = True Else ActiveSheet.Range(Cells(54, c)).EntireColumn.Hidden = False End If

Next c

ActiveSheet.Protect Password:="LD", DrawingObjects:=True, Contents:=True, Scenarios:=True Application.EnableEvents = True ' End Sub

I've attached a sample workbook to show what I am trying to do. I would like the formula to say "if Sheet2!A:A is "MON" and if Sheet2!R:R matches Sheet3!A:A, and if Sheet2!I:I doesn't match any of the values from Sheet1L:L, then I'd like the sum of Sheet2!F:F. It seems pretty simple but I've tried a million different variations of SUMIFS, SUMIF, IF, AND, etc. and I can't figure it out.

On the attached example i would like a formula in F2 which sums all the values where column equals "Smith" and column B equals "Golf European". I'm assuming it would involve the SumIf formula but i'm not sure if this is the best solution when the criteria is in two columns?

I have 2 Columns of Holiday Dates (Column A is the "FROM" date; and Column B is the 'TO' date) and Column C is Peoples Names corresponding to the Holiday dates:

ie:

COLUMN 1 ; COLUMN 2 ; COLUMN 3 A1= 2 Jan 13 ; B1= 10 Jan 13 ; C1= Tom A2= 4 May 13 ; B2= 10 Jun 13 ; C2= John A3= 7 Jul 13 ; B3= 10 Jul 13 ; C3= Tom A4= 3 Aug 13 ; B4= 25 Aug 13 ; C4= John A5= 6 Dec 13 ; B5= 15 Dec 13 ; C5= John.

I am trying to figure out a formula (eg SUMIF) to calculate and summarize the number of Days Holiday based on a person's name (eg Tom's total Number of days Holiday days) based on the above column configuration of Dates and Names.

I have a large Master Log (One Log to Rule Them All.xlsx) with data from 4 regions that I want to link to 4 Regional Logs (Region 2 03-22-13.xlsx, Region 3 03-22-13.xlsx, etc) and populate based on criteria found in column B, titled "County." Let's assume the county names are "Alpha," "Bravo," "Charlie," etc. So if the county is "Alpha," that row should only be linked to Region 2, if it's "Bravo" or "Charlie," it should only be linked to Region 3, etc.

To further complicate things, the 4 Regional Logs get renamed every week, so I will have to re-do this process weekly, so I need it to be simple and quick, plus we email these regional logs weekly, so I need to be able to break any links, or do something else, so that people around the state will see information, even though they don't have the Master Log. I also need the Regional Logs' formatting, spacing, etc to be identical to the Master Log.

I want this so I can edit the Master Log, and when the individual Regional Logs are opened they are automatically updated with the information from the Master Log, regardless if the Master Log is opened or closed. But I also need the Master Log to reflect changes done individually to the Regional Logs, in case someone else edits things.

I want to create a macro that make the somme of 2 other columns when the nummer of the first column ist double(more than one ) and put the somme in a new Rows. like this :

A B 23 5 23 12 23 2 45 4 45 1 46 3 in the news the resultat in the same columns it´s like this A B 23 19 45 5 46 3

I'm trying to create a SUMIF statement that that has an embedded OR statement within, and am unable to make it work.

Basically I want to say if "column Q" equals one of 2 criteria ("cat" or "dog") then sum the corresponding number in "column P" I've tried the following statement, but it is just resulting in a zero:

=SUMIF(Q9:Q32792,OR("cat","dog"),P9:P32792)

I've seen support on statements with multiple criteria in DIFFERENT columns, but not if searching for multiple criteria in the SAME column.

I need to write a couple of functions. Firstly I want to do a count on column B (project $) depending on the criteria on column A (project description). For example a total count of all values in col B if project description is "P" or "A". [I can do sumif functions but this is a countif and I cannot get this right].

I want to format 3 columns in excel depending on certain value from another column. By example: I have the columns A B C completed with some text. On the column D it will be the numbers 0 or 1. If the number is 0 the background color from A,B,C columns shall be Green, if the number is 1 the color shall be red.

I have tried in conditional formatting with the formula =$D$1=0 but the color of the columns are changing only depending on the value from cell D1. I don't know what is the correct formula. For column A1 I want to check the value from D1, A2 - D2, A3 - D3,...,An - Dn.

I am trying to use a nested INDEX and MATCH array formula to return the value in column C when matching column A and column B, but with a few more criteria.

The range containing all the data

A B C

1 Cat 1 January 1, 2014 John

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

I am looking for the array formula to return the name of the person in column C who is in Cat 1 after the date in column B.

For example; C7 should return "John" because B7 requests "January 15, 2014", which is after the value in B1 C8 should return "John" because B8 requests "February 15, 2014", which is after the value in B1 C9 should return "Andrew" because B9 requests "August 15, 2014", which is after the value in B4

This brings back "John" as desired in C7, but when copying down the table into C8 and C9 both C8 and C9 return Andrew.

I guess this is due to my ">=" condition in the Match formula and it is returning "Andrew" because "Andrew" is also after the date requested, but I cannot for the life of me work out how to get it to work.

I am trying to pull cell values similar to a SUMIF function (SUMIF(range,criteria,sum_range)). For example, in A1 I use a data list created from data elsewhere on the spreadsheet. In the data I created elsewhere, there are 2 columns being used. The 1st column is the information that is being used to create the list and the second column contains specific values (number or text). In the dropdown menu I select an available value (text or number) . When I have selected that value I would like cell A2 to show what the cell directly to the right of it shows from the data I have elsewhere in the spreadsheet as mentioned. I have tried the SUMIF function however it seems to exclude certain values (number or text) and I am not sure what else to use.

I have two sets of two columns which contain numbers as text with trailing spaces and I want to concatenate column A and B and concatenate column F and G, and then compare them, and sumif value in column I if they are the same - giving the answer in the same row as the matching data in column A and B.. but in column J.

I’ve seen sum product and haven’t been able to get it to work at all. I have been adding the trim() function in around the column references to see if that would work but am failing…

A = 2000 text B = 1234 text

F = 2000 trailing spaces, text G = 1234 trailing spaces, text

I = 10,000

Answer in column J

I want 20001234 = 20001234 = 10,000 for the whole column.

I don't want to change the original data as its externally sourced and used for other links etc.. This would save a few hours a week if i ( more correctly, you ) could get this to work.

I am trying to create a formula that will sum up a value in one column if certain critieria residing in other columns are met. I have attached a sample and the criteria.

I'm not sure if this is the correct way to display my sample data, but here it is (how to change it. I looked in the guidelines, but I can't download anything onto this computer, so I can't get the HTML maker)

A B C D E F G H I

1 Descriptor Group Identifier Inventory Total Vaue Invetory Type

Group Total Incomplete Inventory Value

[Code] .......

Here is what I would like in:

The sum of the total value of half finished and unfinished inventory for each group.

So, for group one, it would be 145. For group two if would be 38, and group three would be 316. (displayed in column I)

I think an array formula would do this, but I'm not really sure how this would work.

I've been manually writing IF statements out for ranges of data that could easily be done with a little array work. So I set out to convert all my functions into something more readable and quicker to write. But I ran into a problem. I want to add the values of an array G45:Z45 if the corresponding values in G44:Z45 are less than or equal to P41. So I thought to use a SUMIF:

=SUMIF(G44:Z45,<=+P41,G45:Z45)

That didn't work, in fact, it didn't come error free until I did:

=SUMIF(G44:Z45,"<="+P41,G45:Z45)

But that doesn't add anything up either. From what I can see, the problem lies within the condition. If I simply put P41, it works. The moment I add <= I get a multitude of problems.

I am preparing an analysis of my company's expenses for the last two years by account. One on excel sheet I have the raw data,

bank account, payee, payment date, clear date, amount, payment type

(This is from all accounts for the whole 2 year period.)

In the second sheet, I have a summary schedule setup. The summary is setup like this:

Account # Row 1: Begin Date Row 2: End Date

Payment Type 1 AAAAAAA Payment Type 2 BBBBBBBB

I would like to come up with a formula that will populate AAAAA & BBBBB for a given account during a given time period. For example, I would like it to give me the total of Type 1 payments from account 12 during the period January 2, 2005 through January 27, 2006.

Right now it is only giving me the info for 1 payment type and 1 account. Here is what I have now {=SUM(IF(Detail!$G$10:$G$3942>=R$5,IF(Detail!$G$10:$G$3942

does the SUMIF worksheetfunction in VBA work on Arrays? I know it will work on ranges, and thats all fine, but I am convinced it should work on an array too... for example... Just assume two columns of numbers (A and B) with 13 rows -

Public Sub test() Dim a As Range, b As Range Dim x, y x = ActiveSheet.Range("A1:A13").Value y = ActiveSheet.Range("B1:B13").Value Set a = ActiveSheet.Range("A1:A13") Set b = ActiveSheet.Range("B1:B13") MsgBox Application.WorksheetFunction.Sum(x) 'works MsgBox Application.WorksheetFunction.Sum(y) 'works MsgBox Application.WorksheetFunction.SumIf(x, "1", y) 'wont work - "Object Required" MsgBox Application.WorksheetFunction.SumIf(a, "1", b) End Sub

I also tried Transposing the x and y arrays, but the same result "Object Required".

In the attached, sheet 2 has a formula that pulls the Item (column B) from Sheet 1 based on Instocks (column F) being less than the value in N2. Would it be possible to add a second criteria to the formula in sheet 2? In short, can I pull the item from column B of sheet 1 into sheet 2, where in sheet 1, column F values are less than N2 AND where column G values are greater than O2?

I'd love to just vlookup the fill rate value and then filter it down, I know at some point I will be asked to weed it down a bit via a formula instead.

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.

I need to bring in values into one worksheet from another worksheet using row 1 and column A as criteria. I have previously done this using Sumproduct. However the complicated issue with this new worksheet is the setout. The source worksheet (see attached) has the actual data spread across columns. How can I bring in the values to the 'Summary' sheet from the '0607' sheet using the Employee number and the seg code as 2 criterias to lookup and bring in the resulting data (which is spread out in the purple area in the '0607' worksheet).

I then copy and paste 'values' and filter out the 'false' to get my final result.

This has worked in the past, but for some reason that I simply can't figure out, the formula isnt working! I've attached the example, and I've highlighted a number in blue (cell E522 and C103), (that should be being found in the 'LOOKUP' function) but is returning a "FALSE". I have looked over the code and simply can't figure out why Excel isn't returning the right value.

This is obviously happening for a quite a few of my numbers, as my filter result is returning an array that is about 1500 shorter than it should be. I have highlighted E522 as the 'example cell' to look at.