sum of mutliple products

[JR573PUTT]

I want to add any column that have similar names for example:


in column a100 the name is style 1 all stores - the total is 3
in column a3000 the name is style 1 select stores - the total is 5

in cel b3 I want the formula to calc 3+5=8.

Understanding the a100 + a3000 would work, I have about 10,000 rows which would take all day to type in the formulas as the nex formula in b4 would not neccesarily be a101 + a3001.

Any ideas?

[Paul]

I want to add any column that have similar names for example:
in column a100 the name is style 1 all stores - the total is 3
in column a3000 the name is style 1 select stores - the total is 5

in cel b3 I want the formula to calc 3+5=8.

Understanding the a100 + a3000 would work, I have about 10,000 rows which would take all day to type in the formulas as the nex formula in b4 would not neccesarily be a101 + a3001.

So are you trying to count the occurrences of similar values, meaning, there are 3 total occurrences of "style 1 all stores" and 5 total occurrences of "style 1 select stores"?

If so, try COUNTIF with wildcards, for example:

=COUNTIF(A100:A3000,"*style 1*")

Or do you have a name in column A and values in another column that you want to sum?

For this you should be able to use SUMIF with wildcards, for example:

=SUMIF(A100:A3000,"*style 1*",B100:B3000)

[JR573PUTT]

I am trying to calc values in different columns for a name in column A.

Can I reference the column vs typing in the name?

Example:

A1 = style 1
A2 = style 3

A300 = style 1 b300 = 1 c300 = 2
A3000 = style 1 b3000 =6 c3000 =6

IN b1 I want the total of every style 1 which is in b300 and b3000 or the answer would be 7.

In c1 I want the answer to be 8.

IN b2 and c2 report but with style 3


And instead of typing in the names - "style 1" , "style 3" I would rather reference cel A1 and A2, otherwise I would have to write the formula over and over typing in the different names.

[Paul]

How about this..

If in A1 you have the text: style 1
If in A2 you have the text: style 3

In B1, use the formula:
=SUM(IF(NOT(ISERR(SEARCH($A$1,$A$10:$A$20))),$B$10:$B$20,""))

In B2, use the formula:
=SUM(IF(NOT(ISERR(SEARCH($A$2,$A$10:$A$20))),$B$10:$B$20,""))

In C1, use the formula:
=SUM(IF(NOT(ISERR(SEARCH($A$1,$A$10:$A$20))),$C$10:$C$20,""))

In C2, use the formula:
=SUM(IF(NOT(ISERR(SEARCH($A$2,$A$10:$A$20))),$C$10:$C$20,""))

** These are ARRAY formulas. After you type the formula into the cell or formula bar, you must press CTRL+SHIFT+ENTER, not just ENTER. **

-- Change your ranges as necessary. For my example, my data range was A10:A20, B10:B20 and C10:C20. Yours is more like A100:A3000, B100:B3000, C100:C3000.

This works because the formula SEARCH(x,y) returns a VALUE error if the "x" is not found anywhere in "y". This function sums only the cells that don't have an error in them.

Final note: If, for example, you put just the number 1 into A1, cells B1 and C1 will sum every cell in A100:A3000 that has a "1" anywhere in the cell - whether that's "Style 1", "Company 1", "1 East 2nd Street", etc.

[JR573PUTT]

This formula worked great.

I am having trouble copying the formula, how do I copy an array formula down multiple rows?

[Paul]

You can still copy them using the fill handle in the lower right corner of the cell currently having the formula, however you may need to update two things in the new cells:

1. Since the formulas (if you used the ones I provided) use absolute references ($), you may need to update at least the criteria part ($A$1 to $A$3, or similar)

2. You'll probably have to select each new cell, press F2 to go into the cell and then press CTRL+SHIFT+ENTER to establish it as an array formula.

[JR573PUTT]

ok, thanks, I am able to copy a few down at a time, if I copy more than 10 it creates a circular, so I just f2. Thanks again.