SUMIF on formatted Cells

[mefisto666]

Hi All,

Please look at the attached file for the following to make sense.
I've been trying to come up with a formula that will A: Find the company in Column A and B: counts the total sales (Col D) for specific Codes on Col C and model.

The reason why is because month to moth the list changes and there are different models , so I can't just create a static formula (like the one on Col E). So, next month Toyota Won't have "Corolla" on Col B, and therefore Won't have 7 on Col C. So I just want a formula that will first find the Company and then will add the total sales for codes that are specified in the formula.

For instance, take toyota. The formula should have the logic that says: First find Toyota, then add Col D IF Col B = camry&Tundra&Tacoma AND IF Col C is equal to 1 and 3. Notice Corolla has a 1, but is not being added on Col E. I tried working out some formulas but The fact that Col A is formatted the way it is they did not work. I'm still learning how to play with ranges, so that might be why I couldn 't figure it out.


any help is greatly appreciated. Thank you.

[MarvinP]

Hi mefisto666,

Your data has merged cells in it which really keeps you from getting the answers you want. See the attached where I've repeated the data and removed merged cells and done a Pivot Table. I think you want this answer but you'll need to repeat your cell data to get it.

[e.isayev]

Hi,
I changed your file a little to create formulas for it, because merged cells creates problem.
Now You may sum according to every criteria in table
=SUM(IF(ISERROR(FIND($C$4:$C$36,J7)),0,1)*($A$4:$A$36=I7)*IF(ISERROR(FIND($B$4:$B$36,K7)),0,1)*($D$4:$D$36)) CSE

Your required calculation might be simple sumifs case but tayota did not let it happen