-
• #677
Does Sumifs(etc only work on a certain number of rows?
Do the ranges need to be sorted? -
• #678
Does Sumifs(etc only work on a certain number of rows? Effectively, No ( Actually Yes, but only limited by the no of rows your version of excel can handle)
Do the ranges need to be sorted? No -
• #679
No but the ranges need to match in size.
Depending on your complexity, you could make the data a table. Then the header name becomes the range and you can sumif(Brian=yes And Derek=156)
If the makes sense?
(Love a table)
-
• #680
They are slow though. If you have loads of them with lots of different conditions and sizeable data sets it will be a pretty slow spreadsheet.
-
• #681
150k rows. It's strange, it works for most cases but has one exception.
-
• #682
You want to sumif on 150k rows?
Kiss goodbye to your computer.
-
• #683
150k rows is starting to push it for excel in my experience, though I'd expect a sumif to work, just be slow. Paste the formula? (might not help, as obviously dependent on the rest of the sheet)
-
• #684
It's way worse. Got approx 200 different Sumifs on those 150k rows which are all in turn driven by 3 different if statements.
It's slow. Pivoting the data has helped but won't apply to all cases. -
• #685
Jesus. Have you put aside the rest of your day?
-
• #686
I could divide the effort by 12 if I just check one month's worth of data...
-
• #687
Rest of your week.
-
• #688
If you *really* want to kill your PC, try SUMIFS with nested arrays.
-
• #689
could you vba then hard code each step of that sumif's? i.e. change the "=sumif(..." to "=sumif(" then replace "=" with "=" one cell at a time via vba?
I think (without proof) that doing one at a time would be quicker than trying to do them all at once, depending on dependencies. You may get something from hard coding the if's as well once they're calculated.
-
• #690
Might not make a difference so don't spend ages on it. Calculation trees are a bit of a mystery at times ...
-
• #691
If you're using vba, why bother with SUMIF formulae at all?
-
• #692
Pivot tables might help - they're a lot more efficient at calculating things
-
• #693
150,000 lines isn't too bad. It'll take a while but unless you're regularly recalculating it probably isn't worth spending too much time trying to make it more efficient.
Sticking it in Powerpivot and using =calculates is probably the best way to go if you know how to use it.
-
• #694
Or vba chop the data up so that you don't need any sumifs - just sums. Don't know how viable that is, but you can vba autofilters on tables, then use subtotal(..., 109) to get the sum ignoring the hidden rows .
You'll have to vba the answer of subtotal away somewhere.
Again, can't prove that this would increase performance, but it feels like it might.
-
• #695
One Sumifs still returns zero even though a manual check confirms there is data. No trailing spaces etc. Oh well. It runs, takes around 5 mins to refresh.
-
• #696
Is it filtering on a date? I seem to remember the exact syntax for that is a bit weird and always takes a couple of goes to get right. The > or whatever needs to go in speechmarks which is a bit strange.
-
• #697
Moar excel/logic help required.
I have a table of contractor placements, each placement is a row and has a unique ID. Next to that ID is a column called Extensions. The format of the extension is to give the placement ID of the previous placement with EXT1, EXT2, EXT3 so on after it.
As each placement has a £ value, what is the easiest way to link them so I can sum the full contract revenue?
e.g.
Col A Col B
Placement Ext ID
55124 53355Ext1 -
• #698
sounds like a wildcarded sumif is in order. If you can provide a few rows of data we can sort it (in the next few mins :)) .
-
• #699
I'm using Visio 2010 to do some early project planning (the brainstorming bit). I want to export the text with associated hierarchy so am using the export function. I select to export to but it is kicking out an XML file. header of the XML file is:
Workbook xmlns:html="http://www.w3.org/TR/REC-html40"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:o="urn:schemas-microsoft-com:office:office"
xmlns="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:bs="http://schemas.microsoft.com/visio/2003/brainstorming"><ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"/
suggesting it is for Excel. How do I get it into excel?
-
• #700
Looks like that XML scheme is called
SpreadsheetML
which you should be able to import into Excel somehow.If not, could you export direct to Excel from Visio? (Tools|Export to Database, then choose Excel)
If they're numeric codes, you can do a '-0' (zero) on them to turn them into numbers once they're pasted in. This will remove any formatting differences