SOLVED

Help with forumla

%3CLINGO-SUB%20id%3D%22lingo-sub-1290866%22%20slang%3D%22en-US%22%3EHelp%20with%20forumla%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1290866%22%20slang%3D%22en-US%22%3E%3CP%3EI%20have%20been%20struggling%20to%20work%20out%20the%20correct%20formula%20in%20the%20attached%20spreadsheet.%20What%20I%20am%20trying%20to%20do%20is%20to%20work%20out%20how%20many%20Job%E2%80%99s%20each%20solar%20provider%20has%20done%20per%20state.%20I%20have%20tried%20V%20%26amp%3B%20X%20look%20up%20as%20well%20as%20some%20advanced%20filtering%2C%20I%20just%20cant%20seem%20to%20get%20it%20right.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EColumn%20A%20of%20the%20spreadsheet%20lists%20all%20the%20solar%20partners%20I%20work%20with%20and%20Column%E2%80%99s%20B-I%20are%20the%20states%20in%20Australia.%20By%20way%20of%20example%20you%20will%20see%20that%20Alex%20Solar%20does%20jobs%20across%20a%20few%20states%2C%20I%20need%20to%20a%20formula%20that%20will%20look%20up%20%E2%80%9CAlex%20Solar%E2%80%9D%20from%20column%20A%20and%20then%20look%20in%20column%E2%80%99s%20B-I%20and%20report%20back%20how%20many%20transactions%20have%20been%20completed%20and%20in%20which%20states.%20I%20can%20see%20that%20Alex%20Solar%20has%20done%202%20transaction%20in%20NSW%20(Column%20C)%20but%20don%E2%80%99t%20know%20the%20formula%20to%20extract%20that%20data.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThe%20data%20set%20is%20small%20extract%20of%20about%202000%20lines.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWould%20really%20appreciate%20any%20help%20or%20direction%20you%20could%20provide.%3C%2FP%3E%3CP%3ERob%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1290866%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1290957%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20forumla%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1290957%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349713%22%20target%3D%22_blank%22%3E%40robwill100%3C%2FA%3E%26nbsp%3BFirst%2C%20consider%20to%20change%20the%20formula%20that%20plugs%20the%20state%20code%20in%20the%20state%20column%20so%20that%20it%20puts%20the%20number%201%20instead.%20It's%20so%20much%20easier%20to%20calculate%20with%20numbers%20than%20with%20texts.%20SUMIF%20is%20then%20the%20solution%20to%20your%20problem%2C%20I%20believe.%20It's%20demonstrated%20in%20the%20attached%20file.%20By%20the%20way%2C%20I%20needed%20to%20clean-up%20the%20data%20first%20as%20your%20original%20file%20contained%20circular%20references.%20Also%20created%20a%20structured%20table%20in%20order%20to%20make%20it%20a%20bit%20more%20more%20flexible.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1290979%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20forumla%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1290979%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349713%22%20target%3D%22_blank%22%3E%40robwill100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThat%20could%20be%20a%20Pivot%20Table%20if%20clean%20the%20source%20data%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20364px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F183003i2F789584A344063D%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1291086%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20forumla%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1291086%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349713%22%20target%3D%22_blank%22%3E%40robwill100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20got%20this%20from%20your%20question.%3C%2FP%3E%3CP%3EYou%20want%20to%20find%20out%20which%20partner%20has%20done%20how%20many%20projects%20in%20certain%20states.%20If%20this%20is%20the%20case%2C%20just%20go%20for%20the%20pivot%20table%20.%3C%2FP%3E%3CP%3EYES%20IT%20LL%20HWLP%20YOU%20AND%3C%2FP%3E%3CP%3ECLEAN%20YOUR%20DATA%20PLEASE.%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fgyankosh.net%2Fmsexcel%2Fhow-to-use-pivot-tables-in-excel%2F%23gsc.tab%3D0%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fgyankosh.net%2Fmsexcel%2Fhow-to-use-pivot-tables-in-excel%2F%23gsc.tab%3D0%3C%2FA%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1301918%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20forumla%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1301918%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20your%20help%2C%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECheers%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1301920%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20forumla%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1301920%22%20slang%3D%22en-US%22%3E%3CP%3EThanks%20for%20your%20help.%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EOut%20of%20interest%2C%20how%20do%20you%20clean%20your%20data%3F%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1301975%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20forumla%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1301975%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349713%22%20target%3D%22_blank%22%3E%40robwill100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EBy%20removing%20of%20all%20formulas%20within%20source%20data%20range%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1302010%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20forumla%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1302010%22%20slang%3D%22en-US%22%3E%3CP%3EHi%20Sergi%2C%20I%20still%20seem%20to%20be%20having%20trouble%20removing%20all%20of%20the%20formula's.%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20the%20attached%20extract%20I%20have%20not%20removed%20any%20formula's%2C%20when%20you%20click%20on%20a%20blank%20cell%20you%20can%20still%20see%20the%20formula%20even%20though%20there%20is%20no%20result.%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20highlight%20column%20E%20it%20will%20show%20a%20count%20of%209%20as%20every%20cell%20has%20a%20formula.%26nbsp%3B%3C%2FP%3E%3CP%3EIf%20I%20copy%20and%20use%20paste%20special%20to%20paste%20only%20the%20values%2C%20the%20formula's%20are%20removed%20and%20the%20blank%20cells%20in%20column%20E%20show%20no%20formula%20however%20when%20I%20highlight%20the%20column%20again%20it%20is%20still%20counting%209.%20I%20assume%20there%20is%20something%20hidden%20there%20that%20I%20am%20having%20trouble%20removing.%3C%2FP%3E%3CP%3EWhat%20am%20I%20doing%20wrong%3F%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EAppreciate%20your%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F521%22%20target%3D%22_blank%22%3E%40Sergei%20Baklan%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1302043%22%20slang%3D%22en-US%22%3ERe%3A%20Help%20with%20forumla%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1302043%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F349713%22%20target%3D%22_blank%22%3E%40robwill100%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EFormulas%20are%20returning%20some%20string%20or%20empty%20string%20%22%22%2C%20thus%20all%20cells%20in%20the%20range%20have%20some%20text%20value.%20Empty%20string%20doesn't%20mean%20blank%20cell%2C%20that%20is%20the%20cell%20with%20text.%20If%20copy%20and%20paste%20as%20values%2C%20empty%20strings%20also%20will%20be%20pasted.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EExcel%20counts%20them%20as%20cells%20which%20have%20some%20values%2C%20i.e.%20not%20empty.%3C%2FP%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22image.png%22%20style%3D%22width%3A%20312px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F183893i2C3098ECECD6BABD%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22image.png%22%20alt%3D%22image.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CP%3ESecond%20formula%20counts%20the%20range%20ignoring%20empty%20strings.%3C%2FP%3E%0A%3CP%3EThus%20you%20need%20to%20remove%20all%20formulas%2C%20not%20copy%2Fpaste%20values%20they%20return.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

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
Highlighted

@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.

Highlighted
Best Response confirmed by robwill100 (Occasional Contributor)
Solution

@robwill100 

That could be a Pivot Table if clean the source data

image.png

 

Highlighted

@robwill100 

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

Highlighted

Thanks for your help, greatly appreciated.

 

Cheers

 

@Riny_van_Eekelen 

Highlighted

Thanks for your help. 

 

Out of interest, how do you clean your data??

 

@Sergei Baklan 

Highlighted

@robwill100 

By removing of all formulas within source data range

Highlighted

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.

 

 

 

@Sergei Baklan 

Highlighted

@robwill100 

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.

image.png

Second formula counts the range ignoring empty strings.

Thus you need to remove all formulas, not copy/paste values they return.