Sumif Criteria Based On Combination Of 2 Cells

Sep 19, 2012

I have different products with different sizes becoming operational in various months & years. I need to sum those sizes that are operational in a given month/year. In the example below, I have

-product A with 100 unit size, operative since 2007/03
-product C with 200 unit size, operative since 2007/10

So in the table with months on left, and years on top,
from 2007/01 to 2007/02, value must be 0,
from 2007/03 to 2007/09, value must be 100,
from 2007/10 to 2008/06, value must be 100+200=300 and so on.

For this specific example, I tried the following:

Code:
E2 = D2&TEXT(C2,"00")
fill to E7
B11 = sumif(E2:E7,"

View 2 Replies


ADVERTISEMENT

Formula Value Based On Combination Of Two Other Cells?

Mar 10, 2014

Modifying my formula to work. I'm trying to have the value of Cell B2 based off the combination value of two other cells.

[Code].....

View 2 Replies View Related

Sumif & Sumproduct Combination

Oct 9, 2009

I need combining sumif & sumproduct. I have attached a file which explains what I need.

View 2 Replies View Related

SUMIF Based On Two Criteria

Aug 8, 2006

I want a formula to sum all values if two different criteria match. I've attached a sample below. The TaskID and the Time Entry ID are the two data points I'm searching for and the totals column is where my sum statement would be. The numbers are being pulled from another worksheet.

View 7 Replies View Related

SUMIF Based On Two Ranges With Two Criteria

Jul 22, 2002

The following table is used in the sumif.

I would like the sumif to use the range with the names, and the range with the dates.

I have a GUI in which the user enters a name and a date and the $ amount earned for that week should appear.

The criteria is the name and the date.

******** ******************** ************************************************************************>
Microsoft Excel - Business Book.xls___Running: xl2000 : OS = Windows Windows 2000
(F)ile (E)dit (V)iew (I)nsert (O)ptions (T)ools (D)ata (W)indow (H)elp (A)boutP20=
IJKLMN3******4michaelb1*14$400.007/21/20025Michaelb1215$450.007/21/20026

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

View 9 Replies View Related

Sumif Based On 2 Row And 1 Column Criteria

Apr 18, 2014

A1:A10 = Criteria Range 1
B1:B10 = Criteria Range 2
C1:E1 = Criteria Range 3

How to sumif all 3 criteria are met?

View 7 Replies View Related

Sumif(s) Based On Vertical And Horizontal Criteria

Aug 22, 2011

I have a table with

Column A - Suppliers

Columns B to M products with a product group (in row 1)

Prod Group..Core.......Core........Outer........Inner.........Core
Supplier......Type A......Type B.......Type A........Type B.......Type C
AB Ltd........1000.........2000..........500.............750...........5000
CD Ltd........3000.........5000..........100.............950...........8000
AB Ltd........2000.........4000..........600..............800..........7000

I would like to know how to sumif when for eg supplier is "AB Ltd" and the product type is "Core" in this eg = 21,000 (how to paste a table)

View 9 Replies View Related

SUMIF With Two Text Based Criteria Using INDIRECT

Apr 23, 2008

I have a need to sum values in the right column of a 2 column table based on multiple criteria of the values in the left hand column. The table has 500 rows.

The two criteria are:
1 - that the cell contains a specific text string ("product a")and
2 - that the cell contains a text string for the month in question ("Jan")

The other limitation I have to work with is that the formula is in a different workbook to the source data. I have a worksheet that lists the external workbook/sheet and have been using INDIRECT in the DSUM.

I read through the examples here:
[url]

Basically they recommended DSUM as the most efficient.

I can get the DSUM working, but it needs the workbook to be open before it can evaluate the expression and therefore I get an error when I select to update values upon opening the workbook.

So in summary I wondering if anyone can show me how to write a SUMIF formula based on the above criteria.

View 9 Replies View Related

How To Use SUMIF On Consecutive Cells Matching Criteria

May 28, 2009

How would I write a sumif function to only add consecutive cells that match the criteria? Here is the formula I am using right now, but it adds all of the cells that match the criteria, not just the consecutive ones. Also they need to be consecutive cells from the current cell (in the formula below i would be referring to A80)

=IF(A81<>A80,SUMIF(A$5:A80,A80,H$5:H80),"")

More data:

Column A is a group of dates, but not in order, they are slightly mixed up
Column H is a monetary value.

View 8 Replies View Related

Formula- To Pull Cell Values Similar To A SUMIF Function (SUMIF(range,criteria,sum_range))

Oct 25, 2007

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.

View 9 Replies View Related

Searching Combination Of The Criteria

Jan 17, 2010

I have created a program where there is a spreadsheet containing all of the items in a loan tools store, which are issued out to people. I have created a "Search/Find" function within the "Issued Items" sheet. Within this search function, you are able to search all or any combination of the following criteria: Serial; Description; Name; Location. For example: If you just type data in the "Serial" field, it will just search that column and select the cells which contain that value.

The problem I am having is when searching multiple criteria, each and every cell in the columns which are searched is selected. Whereas, I would like only the cells which match all of the criteria to be selected.

For example: If I was to type "1" into the "Serial" field, "2" into the "Description" field, "Liam" into the "Name" field, and "Workshop" into the location field:
Current: Serial column is searched and all cells with "1" in are selected. Description column is searched and all cells with "2" in are selected. Name column is searched and all cells with "Liam" in are selected. Location column is searched and all cells with "Workshop" in are selected.
What I Would Like: Program to search each of the specified columns and only select data which meets the searched criteria. For example: Rows 20 & 28 to be selected as they both contain, Serial "1", Description "2", Name "Liam", Location "Workshop".
Note: The sheet will have password protection.

View 10 Replies View Related

Multiple Criteria SUMIF Using Column As Negative Criteria?

Mar 24, 2014

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.

View 1 Replies View Related

Setup Sumif Statement With Two Criteria Where If Second Criteria Is True

Nov 25, 2013

I am trying to set up a sumif statement with two criteria where if the second criteria is true, the total in the sum range returned is divided by two.

I currently have this:

=SUMIF($G$6:$G$41,"digital",I6:I41)

I want to add in "digital/creative" as a second criteria (from the same criteria range) but I only want 50% of the result of these to be totalled.

is this possible?

View 6 Replies View Related

SUMIF - Summing Data From Several Specified Cells Based On Data Of Corresponding Cells?

Aug 6, 2014

On the "CurrentCustomer" worksheet tab I am trying to accomplish the following:

When the merged cells in the F column (F3, F25, F47...F443) equal the word "Contract", I need the corresponding total cells in the E column (E23, E45, E67...E463) to add up, if and only if the corresponding F column merged cell equals "Contract".

For example, using the first three ranges, if F3, F25, & F47 all equal "Contract", I need the corresponding E23, E45, & E67 to add up. If F3 & F47 equal "Contract", but F25 equals anything other than contract I need E23 & E67 to add up only.

View 3 Replies View Related

Fill Cells Based On Corresponding Cells Matching 3 Criteria

Jul 1, 2008

I am working on a spreadsheet for a shoe company. I have separate columns for the size, model, color, and item number of a shoe. I get everything except for the item number from a written document; I then have to find the item number for the shoe from another excell document called the Master List.

I was hoping there would be a way to have Excell auto-fill the item number for me. For example, if a shoe is a Red, Athens (the shoe model),size 12, its item number (which can be a pain to find) listed in the row of the Master List is aaabbb. So I want to just enter in the size, color and model number, and have Excell find the item number for me, and fill it in.

I have enclosed an example. Sheet 1 is the sheet I would be working on. Sheet 2 is a portion of the Item master list, which is actually 50k lines.

View 8 Replies View Related

Check A Combination Of Two Cells

Aug 18, 2009

I would like to check a combination of two cells, if these two cells are both empty (not zero, just blank) then it will return a blank in another cell. I tried using AND but am unsure how it works. I would like to use a "Case" Function.

Function FirstCheck(Count1, Count2)
Select Case FirstCheck
Case Count1 = "", Count2 = ""
FirstCheck = ""
Case Else
FirstCheck = Abs((Count1 - Count2) / (Count1 + Count2))
End Select
End Function

View 3 Replies View Related

Text Output Based On Combination Of Cell Values?

Mar 9, 2014

I'm tying myself in knots when trying to combine logical formulas to return a text value based on multiple cell values.

Cell A1 can equal 1-10, and cell B1 can also contain the values 1-10.

I want C1 to display the text "YC" if B1 value is equal to 9 or 10. I want C1 to display the text "SC" if B1 value is 5-8, and A1 value is 4-10. Any other combination of values should return a blank cell.

I tried splitting this into two components before trying to make it fit into a single equation, and i can achieve the first condition of display "YC" =IF(AND(A1>=9,A1

View 3 Replies View Related

Count & Sort Numbers Based On Combination Of Occurences

Jan 13, 2008

I have provided an attachment. what I am trying to accomplish. I am trying to have a worksheet that if I input multiple 3 number combinations into the input cell range, after pressing the sort button, it would then sort, rank and count each 3 number combination for me. So as my attached file illustrates, the input cells would be A9:D14. In this sample the ranking consists of cells A19 - A31 as the ranking columns. Cells F19 - F31 show the counted and sorted results and are ranked accordingly. I need a sort button as illustrated in cell F10 to make the worksheet function after the 3number combinations are inputted in cells A9:D14. How do I get started to make this work? I do not know VBA codes or macros so I will need guidance along the way if this is what is needed. I do have some working knowledge of formulas (e.g. countif, rank, etc.)

View 8 Replies View Related

Matching Combination Of Two Cells With A Table And Return Value

May 8, 2014

As shown in the attachment, I'm trying to return value when matching cell B7 + D5 with cell G5:I7. Is there any way to do so?

Attached File: File Type: xlsx Book1.xlsx‎

View 3 Replies View Related

Projected Date Formula From A Combination Of A 2 Cells

Aug 3, 2009

I am trying to make the "Next Phase up DATE" cell automatically configure accorfing to the Issue date and the PHASE 1, PHASE 2, PHASE 3 Selection.

Phase 1 last for 14 days
Phase 2 last for 21 days
Phase 3 has no phase up date

So if I put 1 July 09 in Issued Date cell and select Phase 1, the "Next phase up date will be 15 July 2009. If 1 July 09 in Issued Date cell and select Phase 2, the "Next Phase up date will be 22 jul 09. If 1 July 09 in Issued Date cell and select Phase 3, the "Next Phase up date cell will be (N/A or something to that nature.) The Doortag portions are refrenced to the upper portions, so I only need the formula for (merged)CDE10 & (merged)CDE32

View 3 Replies View Related

Find Number Combination In A Group Of Cells

Oct 28, 2011

I'm looking for another excel game changer (for the work I do anyway).

I have a dollar amount, and I want to know if any combination of dollar amounts in a particular range of cells will equal that dollar amount. Is this possible in Excel?

Example: I have 20 different dollar amounts in a column. I want to know what combination of those 20 different dollar amounts, if any, will equal $257.97. The dollar amount I'm looking for and the numbers in the range will change with each use. I'm hoping for a formula, but VBA will work too as I could just make a template and copy / paste the numbers in.

View 6 Replies View Related

How To Copy Cells Based Upon Criteria

Dec 22, 2009

Here's what we need to do:

We are compiling one master spreedsheet from many smaller ones and I need to figure out how to copy them quickly and efficiently. For example:

Small Spreadsheet:

Column B is marked as P, M, C or S. This represents the type of the account.
Column C is the name of the account.

Master Spreadsheet:

We have a column for each account type (P,M,C,S)

Can we use a formula/macro to automatically copy columns B and C from the small sheet and organize them accordingly in the Master Spreadsheet?

For example:

Small spreadsheet:

P Account Name1
M Account Name 2
M Account Name3
C Account Name4
M Account Name5

would appear in the Master Spreadsheet as:

P M C
Account Name1 Account Name2
Account Name1 Account Name3 Account Name4
Account Name1 Account Name5

View 10 Replies View Related

VBA To Fill In Cells Based On Criteria

Apr 4, 2009

I just stumbled onto this forum while Googling around, and it looks great. I'm pretty new to VBA coding so please bear with me. Effectively what I'm looking to do in this mock-up file, is anywhere the currency is "EUR" in column A, I'd like the relative cell in Column G to change to "INSERT TEXT", else I want it to untouched (i.e. to keep the comment it currently has).

View 6 Replies View Related

Copy Cells Based On Criteria

Jan 29, 2010

I have data like in excel, which I uploaded and I need "simple" thing, If in column G is text Australia then copy from D column each cell which has "AUS1*" + copy the same number cell from C column and copy it into cell A and B. I hope I wrote it understandable

View 2 Replies View Related

VBA Copy Row Based Upon 2 Cells With 3 Criteria

Aug 29, 2012

I'm not sure why this doesn't work on the dummy data?

Problems:It doesn't recognize the September 2011 datesIt is supposed to pull dates between July and August (i.e. 2 dates) and pull everything except those dates.

The date strings have to be variable based since it can't be hard coded.

Code:
Sub copyrow()
Dim rc As Integer, row As Integer, i As Integer
Dim mm As String, fdt As String, pdt As String, mo As String, yr As String
Dim Date1 As String, Date2 As String
mm = Month(Date) - 1
mo = Format(Now(), "mm") 'ex. "08"
yr = Format(Now(), "yyyy")
Date1 = mm & "/01/" & yr
Date2 = mo & "/01/" & yr

[code].....

View 2 Replies View Related

Sum Range Of Cells Based On Criteria?

Sep 27, 2013

I have the following data

Country
Aug-13
Sep-13
Oct-13
Nov-13

UK
3
5
3
2

USA
10
14
16
13

France
1
5
7
9

What I would like to do is sum a specific range of this data based on three criteria. Each criteria is in its own cell.

- Start Date
- End Date
- Country

So if I wanted to sum from 'Sep-13' to 'Nov-13' for 'USA', the formula would give me the answer 43.

View 4 Replies View Related

Sum Cells Based On Row And Column Criteria

Apr 30, 2014

I am really struggling to summarise weekly budget data into period budget data by subcategory.

I have column headings A3 being Subcategory B3 being GL Code & C3 being GL Name - where column B & C are distinct values but column A are not. And then in D2 I have the period no e.g. P01, with the Week no in D3. I want to sum all cells that are in a particular subcategory and period - the data set is broken down by weeks.

View 4 Replies View Related

Locking Cells Based On Criteria

Jun 28, 2006

I have a query in relation to locking cells to disallow editing based on a dynamic range. The VBA code I have thus far is this :

Sub PasteForecastWO()
rowarr = Array(10, 26, 28, 69, 72, 79, 81, 87, 89, 104, 106, 114, 116, 122)
For Each ce In Range("S8:AD8")
If ce = "ACTUAL" Then
For i = LBound(rowarr) To UBound(rowarr) Step 2
Range(Cells(rowarr(i), ce.Column), Cells(rowarr(i + 1), ce.Column)).Value = Range(Cells(rowarr(i), ce.Column), Cells(rowarr(i + 1), ce.Column)).Value
Next i
End If
Next ce
End Sub

What I would like to do is lock the range that applies as the variable "ACTUAL" moves or changes along the range S8:AD8. I have attached the relevant sheet so that you may get a clearer picture.

View 7 Replies View Related

Mandatory Cells Based On Criteria

Dec 17, 2007

I need a macro that will check that the cells B1,C1,D1,E1 and one of the cells F1,G1,H1,I1 are filed out upon entry in A1.

If Not IsEmpty(.Range("A1")) Then
If WorksheetFunction. CountA(.Range("B1,C1,D1,E1")) <> 4 Then
MsgBox "All cells in row 1 are not filled out"
Application.Goto .Range("A1:I1")
Exit Sub

But how do I get it to also check that either F1 or G1 or H1 or I1 is also filled out. If possible I also would like it to beimpossible to add text in more then one of the cells F1:I1. Summary, upon entry in A1 I want cells B1:E1 plus one of the cells in F1:I1 to be mandatory, so in total there will be 5 mandaory cells.

View 2 Replies View Related

Merge Two Cells Based On Criteria

Dec 19, 2007

The begining portion of this works fine, I have it moveing data from one column to another, but I then need it to combine two rows (BW and P) and then place that into column (O)...

View 3 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved