Forum Discussion
Help with forumla
I have been struggling to work out the correct formula in the attached spreadsheet. What I am trying to do is to work out how many Job’s each solar provider has done per state. I have tried V & X look up as well as some advanced filtering, I just cant seem to get it right.
Column A of the spreadsheet lists all the solar partners I work with and Column’s B-I are the states in Australia. By way of example you will see that Alex Solar does jobs across a few states, I need to a formula that will look up “Alex Solar” from column A and then look in column’s B-I and report back how many transactions have been completed and in which states. I can see that Alex Solar has done 2 transaction in NSW (Column C) but don’t know the formula to extract that data.
The data set is small extract of about 2000 lines.
Would really appreciate any help or direction you could provide.
Rob
8 Replies
I got this from your question.
You want to find out which partner has done how many projects in certain states. If this is the case, just go for the pivot table .
YES IT LL HWLP YOU AND
CLEAN YOUR DATA PLEASE.
https://gyankosh.net/msexcel/how-to-use-pivot-tables-in-excel/#gsc.tab=0
By removing of all formulas within source data range
robwill100 First, consider to change the formula that plugs the state code in the state column so that it puts the number 1 instead. It's so much easier to calculate with numbers than with texts. SUMIF is then the solution to your problem, I believe. It's demonstrated in the attached file. By the way, I needed to clean-up the data first as your original file contained circular references. Also created a structured table in order to make it a bit more more flexible.
