Multiple Criteria Sum If

[Gutierrez]

Hello all ... I have a spreadsheet "header" that contains some data in it ... range A:T contain data i would liek to sum .. by column ... and range U:Z has some other data that i would like to use as criteria ... ... and the last thing i would like to do is use the column headers as the second criteria

here is an example ...

header1 header2 header3 header4 header5
3000 2000 800 1500 123
5000 1800 400 600 123
500 6000 200 1200 100

so here is the sample data on another sheet i have this

header1 123 ?????

i want the question marks to give me the sum based on the header name and the number 123 in header5

??? should be 8000

i hope this makes sense.

thanks in advance

 

[Bong]

It seems to me you only have one criterion per column. That means you can use the simple DSUM function on the sheet, itself. On the other hand, if you're already building a macro, why not just nest an IF block inside a row-wise FOR...NEXT loop inside a column-wise FOR...NEXT loop?

_________________
Bob Rashkin

 

[Gutierrez]

is this in your opinion the best solution ... i was thnking if i could do a sum it .. and the way the logic goes is select the range that has my criteria ... in this example header5 ... then select my criteria 123 , and then use the column heading to select the column in which to sum based on criteria ...

 

[SkipVought]

Hi,

I used named ranges using Insert/Name/Create - Create names in top row...
[tt]
=SUMPRODUCT((header1)*(header5=123))
or
=SUMIF(header5,123,header1)
[/tt]
either returns 8000


Skip,

_[red][/red]

 

[Gutierrez]

ok ... i have data in a spreadsheet ... columns A:T ... with a header row ... data also exist in columns U:Z this also has a header but unimportant ... on anothe sheet in this same workbook .. i have for lack of a better word a summary page ... and as an example ... cell B7 contains the text "Book Value" and the cell C7 contains the number 1500 ... now ... in column U of the first sheet there are several cells with the value 1500 .. what i would like to do is ... that for every instance of the value 1500 in column U .. that i sum those same instances in column D which in this case the header equals "book Value" and the sum of these cells should be placed in cell D7 of the second sheet.

here is even more clarification ... this is the formula that gives me what i need:

=SUMIF(Header!V6:V2000,Specifications!C7,Header!I6:I2000)

my problem is that i need the range to be dynamic .. based on the contents of cell B& .. which in this case is equal to "Book Value" ... Column I heading equals "Book Value"the other range and criteria is not a problem just the last range in the sumif formula .. if i could some how direct the range to be set by matching the value of cell B7 and returning the column from a range A6:T6.

 

[Zathras]

Using the sample data from your first post entered into A1:E4 of Sheet1, I set up the following on Sheet2:
[tt]
A6: 'header1
B6: 123
C6: =DSUM(Sheet1!$A$1:$E$4,LOOKUP(A6,Sheet1!A1:E1,0)+1,$E$1:$E$2)
E1: 'header5
E2: =B6
[/tt]
The value of Cell C6 is 8000, per your spec.