Is Number help...

cke

New Member
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

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
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?

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)

cke said:
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.

Replies
0
Views
103
Replies
1
Views
320
Replies
4
Views
150
Replies
4
Views
136
Replies
1
Views
98

1,203,355
Messages
6,054,920
Members
444,759
Latest member
TeckTeck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back