Apr 07 2020 11:25 PM
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
Apr 08 2020 12:17 AM
@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.
Apr 08 2020 12:33 AM
SolutionApr 08 2020 01:21 AM
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
Apr 12 2020 11:04 PM
Apr 12 2020 11:05 PM
Apr 12 2020 11:44 PM
By removing of all formulas within source data range
Apr 13 2020 12:05 AM
Hi Sergi, I still seem to be having trouble removing all of the formula's.
In the attached extract I have not removed any formula's, when you click on a blank cell you can still see the formula even though there is no result.
If I highlight column E it will show a count of 9 as every cell has a formula.
If I copy and use paste special to paste only the values, the formula's are removed and the blank cells in column E show no formula however when I highlight the column again it is still counting 9. I assume there is something hidden there that I am having trouble removing.
What am I doing wrong?
Appreciate your help.
Apr 13 2020 12:30 AM
Formulas are returning some string or empty string "", thus all cells in the range have some text value. Empty string doesn't mean blank cell, that is the cell with text. If copy and paste as values, empty strings also will be pasted.
Excel counts them as cells which have some values, i.e. not empty.
Second formula counts the range ignoring empty strings.
Thus you need to remove all formulas, not copy/paste values they return.
Apr 08 2020 12:33 AM
Solution