Sumifs With Horizontal And Vertical Criteria Range?

Mar 6, 2014

I am trying to devise a formula for the data set below (into a different tab). The table below is something like what I have:

Day 1
Day 2
Day 3
Day 4

[Code]....

Though my table is a bit more complicated (it has empty rows in between and a much larger data range).

I need to reflect this in another tab in the following format:

Day 1
Day 2
Day 3
Day 4
Spoke 1
Spoke 2
Spoke 3

As I fill in data in the first tab, I would like this tab to automatically pick it all up. Is there any way I can do this? I may add rows etc. so I do not want a simple addition.

View 6 Replies


ADVERTISEMENT

SUMIFS - How To Sum Data Using Both Horizontal And Vertical

Jul 20, 2012

I have a huge data table, and I just can't seem to find a way to sum the numbers the way I want.

Here's an example of the table:

___ A B C A B C A B C A B C A B C
Jan 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Jan 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Feb 3 4 5 6 3 4 5 6 3 4 5 6 3 4 5
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Feb 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Mar 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
Mar 6 3 4 5 6 3 6 3 4 5 6 3 6 3 4
...
...

But, in essence, I need to sum together every value that's in January AND is in a column labeled "A".

I could just write up a bunch of "sumif=" functions but I feel like there's a simpler way.. I tried using "sumifs=" but I don't think it can handle horizontal AND vertical criteria...

View 14 Replies View Related

Sum Range Using Both Horizontal And Vertical Criteria?

Sep 19, 2013

I am trying to create a formula that will sum a range based on both horizontal and vertical criteria using the following data:

1
2

1
20%
25%

2
40%
25%

3
40%
50%

For example, if the horizontal criteria were "1" and the vertical criteria were "2", I need to sum the 20% and 40% amounts (60%). If the horizontal criteria were "2", and the vertical criteria were "3", I would need to sum the 25%, 25%, 50% (100%). I have tried using SUMIFS functions, but they do not appear to work with both horizontal and vertical axis criteria (I receive a #VALUE error).

View 1 Replies View Related

Lookup With Vertical And Horizontal Criteria

Jul 22, 2014

See attached for a clearer view : LOOKUP SEARCH.xlsx‎

View 10 Replies View Related

Lookup Table Using Horizontal And Vertical Criteria?

Mar 6, 2014

comp1.PNG

Attached is a screenshot. Basically I need to know that if PersonA has say 13.74 CPH and $776 DPH - what the incentive would be ($575)...

View 3 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

Find Data Based On Horizontal And Vertical Criteria

Mar 21, 2007

I have a spreadsheet that I am trying to create a formula for that will bring back the data found when you compare an X and Y axis. A sample is attached as the data is huge and I figured what ever you all created I could modify.

I need it to bring back the data found when I run my finger down the column till I hit the appropriate row.

View 9 Replies View Related

Transpose Vertical Range Of Data Into Horizontal Range

Jun 17, 2014

Simple transpose question: Lets say I have a verticle range of data from B3:B13. I wish to have code that will transpose that data into a horizontal range at D3:M3, is that possible?

View 5 Replies View Related

SUMIFS With Criteria Range

Oct 31, 2013

Having issues trying to do a sumifs function using multiple criteria to get a final total. I want to sum a list of values that have different transaction types from a transaction table. The only working way I have found is to do a sumif and create a CSE formula for it and drag it down and sum to the total. I am looking for a way to sum the the entire list of transaction types I need from the transaction tab and get to the total value rather than have to use a CSE and then sum. Basically I want to take out the sum of the CSE results....Is there anyway to do this?

View 5 Replies View Related

SUMIFS - Cannot Use The Same Criteria Range?

Dec 23, 2013

Following the syntax of the SUMIFS formula --> Can "criteria_range1" be the same as "criteria_range2" ?

It didn't work for me and I got the result as 0.

View 5 Replies View Related

SUMIFS With Criteria Range?

Dec 19, 2012

I have the following formula that I did:

=SUMIFS(Sheet2!L:L, Sheet2!B:B, 1, Sheet3!A:A, Sheet3!A5, Sheet2!X:X, "LOL", Sheet2!R:R, "delivered")+SUMIFS(Sheet2!L:L, Sheet2!B:B, 1, Sheet3!A:A, Sheet3!A5, Sheet2!X:X, "LOL", Sheet2!R:R, "shipped")

As you can see I have exactly same thing in the first SUMIFS function, and the second one except for the "delivered" and "shipped" criteria. Now, what I would like to do is to create a range that would be used instead of a single criteria.

Actually, I have more than 20 criteria but I don't want to do 20 separate SUMIFS and then adding them. Best would be if I could select a range of criterias (I would like to be updating that list easily in the future).

View 6 Replies View Related

VBA Vertical And Horizontal Lookup?

Feb 25, 2014

I was wondering if it's possible to create a macro that would check the range horizontally and vertically and return the resoults in a userform so that when workbook is open this userform would come up with the information who is due for refresher....

The sample book doesn't show the real size of the original matrix which has 190 employess and about 30 procedures so as you can imagine it's a pain.. to keep it up to date so I need something to flag that for me, yes I can see the red cells but still there's so many cells that I might easly miss some of them....

View 14 Replies View Related

Horizontal Row To Vertical Column?

Apr 2, 2014

I have data in a vertical column how can i equate that data to a horizontal row in other sheet. how to do it by formula?

View 4 Replies View Related

Search Horizontal And Vertical

Sep 15, 2009

What kind of formula do I need that looks for a date and find all the names that have the same character on that particular day

In the example below cell R4 kooks for date R3 in range A1:L15 and find all cells that have a "G" and displays all the corresponding names from C1:L1

I prefer non array-formulas

View 9 Replies View Related

Using Absolute Value For Criteria Range In SumIfs

Jul 8, 2014

So I have a formula as follows:

=SUMIFS(BD:BD,J:J,J15,AD:AD,AD15,ABS(BD:BD),ABS(BD15))

Although it is not working because I cannot use ABS() on a criteria range. is there any other way that I can have this formula sum the values if the absolute value of the number in that row is equal to the absolute value of 1 or more values in the same column but a different row?

View 7 Replies View Related

SUMIFS With Date Range As Criteria?

Dec 9, 2013

I have time-worked spreadsheet use to calculate the hours worked over a year, now I need to sum the hours worked during a given period if they happen to be on a public holiday. I have a column (say A) displaying dates from 1/1/13 till 31/12/13, another column giving the result of the worked hours (say B). I also have a defined list of public holidays which I need to compare with (named Holidays). The working year is "divided" in 13 periods of 28 days each (the last one having 29 days). I was trying to use the SUMIFS function (since i have other constrains that need to be met)
e.g. =SUMIFS(B1:B28,A1:A28,Holidays)

View 9 Replies View Related

SumIfs Statement And Range Of Criteria?

Mar 19, 2014

I'm trying to put together a SumIfs formula to find the total count of procedure codes during a certain time period, for a given market.

I have the Market column, Procedure Code column, Date column, and count of Procedure Codes column.

The range of Procedure Codes I want to total on are in cells C4:F4 and they are:

99307993089930999310

It's fairly straightforward with the dates in the SumIfs as well as markets, but I'm not able to get the range of Procedure codes to work like I want. These 4 codes are just the criteria that will use to pull the totals from, which comes from a much larger dataset.

View 1 Replies View Related

Arranging Vertical Data To Horizontal

Dec 25, 2013

I have not found a suitable solution for arranging vertical data horizontally using VBA

I have number of data in column B arranged vertically in sheet1 how can we arrange the data in sheet 2 column B row 3 horizontally.

The vertical data keeps increasing when new data is added, so VBA code should search to the last line in Sheet1 column B.

All duplicate entries should not be repeated when arranging horizontally.

View 9 Replies View Related

Transposing From Horizontal To Vertical With Intervals

Jan 9, 2014

I've been working on this for days, i still got no solution on what formulas I will put on cells in "To" tab. The excel file is composed of two tabs "To" and "From". We will convert the "From Tab" to "To" format. What formula will I put in D3 to V3 and drag down in "To" tab to convert datas from "From" tab.

View 9 Replies View Related

Horizontal And Vertical VLOOKUP In A Table?

Jan 27, 2014

I have to fill an invoice template, whereby I need to lookup the unit price in a pricing sheet, based on the effective date (horizontally placed) and the specific tasks to be invoiced (vertically placed).

The effective date cell (B1) is the one that can be changed, the formulas in the unit price cells (yellow highlighted) then needs to look up the price for that specific task in the table on the tab "pricing sheet".

I was trying something with INDEX / MATCH, but can't get it to work.

View 4 Replies View Related

Complex Vertical And Horizontal Lookup

Feb 2, 2014

I've got the following complex issue:

...................Trader1 Trader 2 Trader 3 Trader 4 Trader5... Trader20 (Horizontal)
Commodity1 .....0.....2.....0.....1.....1
Commodity2 .....0.....4.....0.....-3.....0
Commodity3 .....1.....0.....0.....5.....0
Commodity4 .....0.....0.....2.....0.....0
.
.
.
Commodity 36 .....2.....1.....1.....0.....0

I have 5 cells that are basically a drop down list of all the traders, so the user can select up to 5 traders.

Once the user selects up to 5 traders (lets say Trader 1, 3 and 5 for our example) I have the following requirements to fulfill:

Displaying all commodities that have non-zero, non-negative values associated with those traders in a list somewhere.

For our example, this would then display:

Commodity 3
Commodity 4
Commodity 1

View 4 Replies View Related

Lookup For Vertical And Horizontal Corresponding Values.

Nov 28, 2008

I have a problem that lookup vertical and horizontal corresponding values when there was duplicate values as it's only returning the first value found. What I want was to lookup the vertical and horizontal corresponding values on the left most & top most column based on the largest values column and also to return the duplicate values under the vertical and horizontal value column in ascending order if it's a duplicate values.

View 4 Replies View Related

Convert Data Horizontal To Vertical

Sep 5, 2009

i have a data in columns and would like to convert into rows

e.g.
11 A B C D E F G H
22 1 2 3 4 5 6 7 8
33 2 3 4 5 6 7 8 9
44 3 4 5 6 7 8 9 0

and result should be
A 22 1
A 33 2
A 44 3
B 22 2
B 33 3
B 44 4
C 22 3
C 33 4
C 44 5
and so on ..

View 6 Replies View Related

Changing Entries From Horizontal To Vertical

Oct 29, 2009

I have a list with entries in one column like this:

sourceText
empty cell
targettext

sourceText2
category2
targettext 2

and I want to change it like this:

sourceTextempty cell target text
sourceText2category2target text 2

Attached sample data. Sheet one as it is. Sheet 2 as I want them to be (I don't need any of the formatting in Sheet 2).

View 3 Replies View Related

Lookup With THREE Conditions - One Vertical / Two Horizontal

May 23, 2012

I have a table where I need to return a value according to THREE conditions:

1. Vertical, which is a shop code for instance

2. And 2 horizontal:
- One: segment (heading level 1)
- Two: product (heading level 2)

E.g. for "Shop1", what's the value for segment "PI" and product "Credit", etc.

An example file attached : fz73m5zEmZS6Y14-C.xlsx‎

The key thing for me is to do this WITHOUT changing data structure, because that really would be a huge pain. I mean, if it's no possible to do without changes (e.g. adding an extra column, etc.), I'll have to live with that I guess. But ideally I would really like to avoid changing data structure.

View 8 Replies View Related

Transpose Vertical Data To Horizontal?

Apr 18, 2013

I have the attached worksheet that I would like to know how to move the data from Vertical to Horizontal

Is there a set function to do this or does it require a macro?

View 5 Replies View Related

Transpose Calculated Value From Horizontal To Vertical?

Sep 4, 2013

I have a series of cash flows as follows:

Year 1 Year 2
Income 100 150
Expense 20 30
Net Income 80 120

I need to take the calculated "Net Income values" and also place them vertically by themselves, like this:

Year 1 80
Year 2 120

I know I can copy and paste the values easily using the "paste special... transpose", but how can I easily do this so that it continues to reference the original values? So that if I change the Income in year 1 of 100 to 120 the vertical Year 1 value would change from 80 to 100?

View 4 Replies View Related

Make Horizontal Data Go Vertical

Dec 13, 2005

a_____b____ c_____ d_____ e______ f______ g_____ h_____ i
1 - 12/1--| 385 -| 15000 -| --etc--|| --etc--|| --etc--|| --etc--|| --etc--|
2 - 12/5--| 256- | 20000 -|
3 - 12/6--| 356- | 11000 -|
4 - 12/5--| -89--| -1000 --|
5 - 12/9--| 500- | 9000 ---|

Where d1:d5 e1:e5 f1:f5 and g1:g5 h1:h5 i1:i5 all have similar data.

Is there a way to have the data from def and ghi fall in line underneath abc with the condition being that there has to be something in the cell?

View 10 Replies View Related

Moving Vertical Data To Horizontal

Apr 20, 2012

I need to do is to move data which is in 3 columns with multiple entries (ID) in first column to single entry in first column (ID) horizontally.

Currently three columns are ID, Code and Reply:

111 A D
111 B F
222 A I
333 B D
333 C F

What I would need is (ID, Code1, Reply1, Code2, Reply2):

111 A D B F
222 A I
333 B D C F

Is there a macro I could use for it?

View 1 Replies View Related

SUMIF With Both Vertical And Horizontal Data

Jan 16, 2009

I need a solution for the equivalent of a SUMIF combining both vertical and horizontal data. The vertical cells align to the horizontal ones, but they're in a different table.

My attempted formula is: =SUMIF($H$22:$H$30,"TRUE",D7:L7)
*note that this is just an example set of data...my real data set is much larger (both rows and columns)

I need to be able to do this without transposing any of my data.

Things I've tried:
- Another option I tried was making D7:L7 a named range and using the transpose function (as an array) within the SUMIF formula above. I received an error.
- I tried using a bunch of IF statements added together (i.e. =IF(H22=TRUE,D7,0)+(H23=TRUE,E7,0)...); this actually works properly, but I get the "formula too long for cell" error when I put them all in (too many characters)

I'm using excel 2003 and windows XP professional.

View 9 Replies View Related







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