Trying to sum a value in column C if value in column A=x and B=y

[JohnAndersen]

I have a decent sized database and I am trying to sum multiple individual's yearly sales credit (current and historical). column C is the value I am trying to sum. Column A contains the last name and column B contains the year I am trying to reference. Does this make sense? Does anyone do something similiar. For reference here is my last attempt:

=IF(AND(NAME="Smith",YEAR=2010),SUM(SALES CREDIT),"YOU DID IT WRONG")

I am trying to use dynamic named ranges for this. Is that the issue?

I am a relative beginner. Thanks in advance for your help!

 

[Aladin Akyurek]

I have a decent sized database and I am trying to sum multiple individual's yearly sales credit (current and historical). column C is the value I am trying to sum. Column A contains the last name and column B contains the year I am trying to reference. Does this make sense? Does anyone do something similiar. For reference here is my last attempt:

=IF(AND(NAME="Smith",YEAR=2010),SUM(SALES CREDIT),"YOU DID IT WRONG")

I am trying to use dynamic named ranges for this. Is that the issue?

I am a relative beginner. Thanks in advance for your help!

Looks like you want...

=SUMPRODUCT(--($A$2:$A$400="Smith"),--($B$2:$B$400=2010),$C$2:$C$400)

Adjust the ranges to suit.

 

[JohnAndersen]

Looks like you want...

=SUMPRODUCT(--($A$2:$A$400="Smith"),--($B$2:$B$400=2010),$C$2:$C$400)

Adjust the ranges to suit.

Thanks for the info, it looks like sumproduct is the way to go. I am trying to use the formula with my currently existing named ranges that would take the please of each A B and C above :

RangeA:=OFFSET('<sheet1>'!$AI$1,1,0,COUNTA(<sheet1>!$AI:$AI),1)

RangeB:=OFFSET('<sheet1>'!$B$1,1,0,COUNTA('<sheet1>'!$B:$B),1)

RangeC:=OFFSET('<sheet1>'!$AM$1,1,0,COUNTA('<sheet1>'!$AM:$AM),1)

I was reading that each array needs to have the same dimensions. Each of these should contain the same dimensions of data, though.

I guess there must be something I am missing.

 

[Aladin Akyurek]

Thanks for the info, it looks like sumproduct is the way to go. I am trying to use the formula with my currently existing named ranges that would take the please of each A B and C above :

RangeA:=OFFSET('<SHEET1>'!$AI$1,1,0,COUNTA(<SHEET1>!$AI:$AI),1)

RangeB:=OFFSET('<SHEET1>'!$B$1,1,0,COUNTA('<SHEET1>'!$B:$B),1)

RangeC:=OFFSET('<SHEET1>'!$AM$1,1,0,COUNTA('<SHEET1>'!$AM:$AM),1)

I was reading that each array needs to have the same dimensions. Each of these should contain the same dimensions of data, though.

I guess there must be something I am missing.

Why don't you have a sheet name? If that's a mistake, what would be that name?

 

[JohnAndersen]

That's strange, I put <sheet1> in each of those formulas. In any event the sheet name in each spot would be "master list"

 

[JohnAndersen]

That's strange, I put <SHEET1>in each of those formulas. In any event the sheet name in each spot would be "master list"

RangeA:=OFFSET('Master List<SHEET1>'!$AI$1,1,0,COUNTA('Master List<SHEET1>'<SHEET1>!$AI:$AI),1)

RangeB:=OFFSET('Master List<SHEET1><SHEET1>'!$B$1,1,0,COUNTA('Master List<SHEET1><SHEET1>'!$B:$B),1)

RangeC:=OFFSET('<SHEET1>Master List<SHEET1>'!$AM$1,1,0,COUNTA('Master List<SHEET1><SHEET1>'!$AM:$AM),1)

 

[Aladin Akyurek]

RangeA:=OFFSET('Master List<SHEET1>'!$AI$1,1,0,COUNTA('Master List<SHEET1>'<SHEET1>!$AI:$AI),1)

RangeB:=OFFSET('Master List<SHEET1><SHEET1>'!$B$1,1,0,COUNTA('Master List<SHEET1><SHEET1>'!$B:$B),1)

RangeC:=OFFSET('<SHEET1>Master List<SHEET1>'!$AM$1,1,0,COUNTA('Master List<SHEET1><SHEET1>'!$AM:$AM),1)

I'm assuming that the real data starts at row 2 while row 1 houses headers...

Define VSize (from Vertical Size) as referring to:

=MATCH(9.99999999999999E+307,'Master List<SHEET1><SHEET1>'!$AM:$AM)-ROW('Master List<SHEET1><SHEET1>'!$AM$2)+1

Define RangeA as referring to:

=OFFSET('Master List<SHEET1><SHEET1>'!$AI$2,0,0,VSize)

RangeB as:

=OFFSET('Master List<SHEET1><SHEET1>'!$B$2,0,0,VSize)

and RangeC as:

=OFFSET('<SHEET1>Master List<SHEET1>'!$AM$2,0,0,VSize)

Now substituting these range names as appropriate in a SumProduct formula or any other would work as expected.

 

[JohnAndersen]

I'm assuming that the real data starts at row 2 while row 1 houses headers...

Define VSize (from Vertical Size) as referring to:

=MATCH(9.99999999999999E+307,'Master List<SHEET1><SHEET1>'!$AM:$AM)-ROW('Master List<SHEET1><SHEET1>'!$AM$2)+1

Define RangeA as referring to:

=OFFSET('Master List<SHEET1><SHEET1>'!$AI$2,0,0,VSize)

RangeB as:

=OFFSET('Master List<SHEET1><SHEET1>'!$B$2,0,0,VSize)

and RangeC as:

=OFFSET('<SHEET1>Master List<SHEET1>'!$AM$2,0,0,VSize)

Now substituting these range names as appropriate in a SumProduct formula or any other would work as expected.

This works perfectly. Thank you so much.

 

[Aladin Akyurek]

This works perfectly. Thank you so much.

Great. Thanks for providing feedback.