Is Number help...

[cke]

Recently I ask help for the following problem...

Colum 1 = Text Account numbers (ie..."1022")
Colum2 = Dollar Amounts

I need a formula to say
if colum 1 has text numbers xxx OR xxx OR xxx (there could be a lot of xxx's) then add all the amounts associated with those numbers

I thought I could do it with: =SUMIF(B6:B1002,"1022",F6:H14) , however, I
need to add more text account numbers ("1022") and this is where I am stumped!

Thankfully someone helped me with the following formula that has changed my life....

=SUMPRODUCT(--ISNUMBER(MATCH($B$1:$B$1502,{1022},0)),$C$1:$C$1502)

I would like to add a second ISNUMBER to the formula, but don't want to screw it up....so it will say...if there is a match in the first and second colums, then all the amounts associated with those numbers.

Any help would be appreciated again!!
Thank you

 

[Norie]

Are you trying to sum the dollar amounts for each account number?

Have you considered creating a pivot table with the account number as a row item and sum of dollar amount as a data item?

 

[Oaktree]

I think you want:

=SUMPRODUCT(--ISNUMBER(MATCH($B$1:$B$1502,{1022},0)),--ISNUMBER(MATCH($D$1:$D$1502,{"A","B","C","D"},0)),$C$1:$C$1502)

 

[Aladin Akyurek]

Recently I ask help for the following problem...

Colum 1 = Text Account numbers (ie..."1022")
Colum2 = Dollar Amounts

I need a formula to say
if colum 1 has text numbers xxx OR xxx OR xxx (there could be a lot of xxx's) then add all the amounts associated with those numbers

I thought I could do it with: =SUMIF(B6:B1002,"1022",F6:H14) , however, I
need to add more text account numbers ("1022") and this is where I am stumped!

Thankfully someone helped me with the following formula that has changed my life....

=SUMPRODUCT(--ISNUMBER(MATCH($B$1:$B$1502,{1022},0)),$C$1:$C$1502)

I would like to add a second ISNUMBER to the formula, but don't want to screw it up....so it will say...if there is a match in the first and second colums, then all the amounts associated with those numbers.

Any help would be appreciated again!!
Thank you

I bet that someone has a name. One thing to note though: If the {...} bit contains just one value, there is no need to include a IsNumber/Match conditional... The above formula with {1022} should be abondoned in favor of:

=SUMIF($B$1:$B$1502,1022,$C$1:$C$1502)

The new question doesn't specify the ranges of interest. Why is that? Secret? The point is that the failure to do so often ellicits abstract formulas you might have trouble to work with. Hence:

=SUMPRODUCT(--(Range1=X),--(Range2=Y),SumRange)

To emphasize once more... For IsNumber/Match you need multiple conditions that must hold for a single range.