Need Help Making a Formula or Correct Function to Use

Hello All,

I am trying to make a formula, but I am having a hard time putting it into words. So I will give an example:


excel.PNG


I would like it to have the format above and only output the the values of certain counties in the column. So in the example Baldwin and Mobile are the counties that I wanted the values for, not Jefferson, so the AL Total will only include Mobile and Baldwin because those values are my relevant information.

What function would I use to set this up? For reference I will be using this on excel sheets with thousands of rows of data, I am tired of filtering and through the data for hours when I feel like excel can do it for me in seconds..

Thanks for any help in advance!

You could probably use SUMIFS or SUMPRODUCT.
Please read the yellow banner at the top of the page if you need more info, and attach a sample.

sample.xlsx

Attaching a workbook for additional help, new to excel so I think it is easier for me to understand when I can see it in action!

There was a slight error in the first formula that I gave you (it's always better to attach a sample file). It should be this in B10:

=SUMIF(B$2:B$5,LEFT($A10,FIND(" ",$A10)-1),C$2:C$5)

which should be copied into B11, and then the other formula goes into B9 , as stated.

Hope this helps.

Pete

Assuming "State Code" is in cell A1, you can use this formula in cell B10:

=SUMIF(B$2:B$5,LEFT($A10,FIND(" ",$A10)-1),B$2:B$5)

and you can copy this into cell B11. Use this formula in cell B9:

=SUM(B10:B11)

Hope this helps.

Pete

Since you said you have 1000's of rows of data, I'm assuming you may have the same county name in different states. If that's the case, to be on the safe side, I would just add this to HansDouwe formula:

=SUM(C$2:C$5*(B$2:B$5=LEFT(A10,LEN(A10)-7))*(A$2:A$5=LEFT($A$9,LEN($A$9)-7)))