Totals without using Pivots???

[Pagla]

Hi all


The problem: I have the same values on different sheets, but with similar criteria as below. Now I need a page that has the total amounts for all the sub types that match the types.

Or simply put, I'm lazy and don't want to have to sift through all the data and add it up "manually".


Data table 1

Type Sub-Typ Amt

HI HILO 250

LO LOLO 50

HI HIHI 500

HI HIHI 500

LO LOHI 100

LO LOHI 100

LO LOLO 50


Bear in mind that i have simplified the example, but the criteria is the same, type and sub-type have amounts found on 3 pages. Now i'm hoping to find a function, or group of functions that would help simplify the process of adding all the exact matches of type with sub-type and adding their amounts into a table showing totals.

i was hoping to not have to resort to pivot tables as i am unsure how to culminate the data from various pages into one PT. Also the end user of the spreadsheet is somewhat,,, lacking in the grey matter. So i'm just needing a simple table that shows all the data on one page. Similar to below...


Totals table

Type Sub-Type Total

HI HIHI ----

HI HILO ----

LO LOHI ----

LO LOLO ----


I've tried using SUMIF with AND, but couldn't do it. The VLOOKUP only looks for the first instance. Then i tried SUMIF with AND and VLOOKUP, turned up messy with no results.

Where have i gone wrong???

Thanks in advance to who ever can crack this one. If anyone is interested, i could mail them the Excel version of the example. I'm using Win XP, excel 2003.

I'm also basically a noob to Excel, totally self taught. Excel intrigues me to the point of losing track of time and spending hours playing around with various functions and grouping them together.


Pagla

 

[keymaster]

Actually, you can use pivot tables for this with very ease. Follow these steps:


1. Menu > Data > Pivot

2. Choose the option, pivot from multiple ranges

3. Add each of the ranges (worksheet tabs) to pivot

4. Press OK and create the final report


Here is some help: http://www.contextures.com/xlPivot08.html

 

[Pagla]

Hi again


Actually i was reading the very same page about pivots last night and found it awesomely helpfull!!!!!

The only thing is, the data is not in the same format on all 3 pages, and i don't want a pivot table output. So i was gonna use a string of vlookup functions, eg; vlookup($a$2,a2:$c$6,3,false) , and copy it down a few cells so in actual fact making the table array smaller. Using this method because vlookup finds the first instance of the match. Repeat same procedure for the sub-type column and get an array of data, then into another table i'd use the IF function with the AND function nested then end up building another table of specific data then simply putting the sum function to add the relevant data. Did i lose anyone?

A bit drawn out, but i hope it gonna work. The plus side is that the original tables of data are always the same size so i can use the $ signs efficiently. Will let you know.


Thanks to Chandoo for such an awesome site!! So helpful and challenging.

Excel is the best!! &)


Pagla

 

[Pagla]

So simple the answer;

IF(AND(A2=$D$2,B2=$E$2),C2,0)

A and B being the criteria, D and E being what to match to(just need to create a table with the relevent criteria), C being the amount. And drag this function down as many times as there are entries. So if there is no match, it will pop up a zero, and if there is a match, it will show the amount found in C. So at the bottom you can put a SUM function to get a total for that page for all the types with sub-types. Repeat process for all pages and feed the totals into the Grand totals page.

I know, i know, a pivot table would be much easier. But, the spreadsheet isn't for my benefit, so i have to accomodate the end user.

Fun madness!!!


Pagla