alternative

  1. Y

    Alternative to Array Formula

    I am using this formula : =IFERROR(MEDIAN(IF((rngProgramme=rngEruCourse)*(rngOwnerType="LA")*(rngStartYearList=rngStartYear)*(rngCreatedDate1>=rngLeadsFrm)*(rngCreatedDate1<=rngLeadsTo)*(rngRating="Null")*(rngDisposition=""),rngAging)),"-") THen Ctrl + Shift + Enter and I have a lot of cells...
  2. J

    Indirect alternative for external workbook

    Hi, I'm struggling with an alternative here. Cells A3:A50 has a list of workbook names in a specific location (this auto updates when a workbook is added or removed) which is where i want to pull my data from - from sheet 2 column B. Each workbook is of the same layout, which makes it a little...
  3. B

    Alternative to this array formula

    Hello I have workbook which is painfully slow and I'm wondering if there is a better way of doing the formulas below so I don't have to keep deleting them then reinserting them... I tried restricting the range to J7:J1400 but it doesn't seem to make any difference...
  4. G

    Sumifs multiple but same criteria, better solution?

    Hi all, Below is an example of a simple sumifs formula. =SUMIFS(A3:A15,C3:C15,0,D3:D15,0,E3:E15,0,F3:F15,0) Is there an alternative to this, bearing in mind more columns could be added but will the same criteria i.e (0)? I am sure the must be an alternative, maybe sumproduct and using a one...
  5. K

    Alternative to ifs function

    Is there another option if you can't use the =ifs command other than using multiple & if over and over? Thank you!
  6. J

    Why does this formula take so long???

    =Countif($a$2:$a$150000,a2) So when copied down in B and pressing F9 takes litterally 10-15 mins to calculate.. I have used this before and it didnt take this long? any suggestions as to why? or an alternative to achieve the same result? thanks J
  7. R

    Alternative to filtering / advanced grouping

    Hi Mr Excel, I need an alternative to filtering. Something like grouping that will give me a handy button on the lefthandside... I understand groups. I select three adjacent rows and do shift-alt-rightarrow and a button appears on the lefthandside allowing me to open or close. But what if the...
  8. S

    Sumproduct or alternative

    Hi, I have been using a sumproduct formula for some years, and although it works fine I was wondering if there was an alternative way of getting the same result. In C8 copied down =SUMPRODUCT(($A8>=$A$3:$A$4)*1,($B8<=$B$3:$B$4)*1,($C$3:$C$4)) to show how many are in each hour <tbody> Start...
  9. M

    SumProduct Alternative - Performance Issues

    Hi, I quite often use SumProduct to perform calculations where I have multiple criteria in rows and at least 1 criteria in the columns, the formula may look like: =SUMPRODUCT((Column1 = X)*(Column 2 = Y)*(Row1 = A)* (Data Set)) The number of rows in Column1/2 could be 5000 plus and the Row1...
  10. J

    Anything faster than =LOOKUP(2,1.... ?

    Hi, i use these formulas quite a lot for looking up previous match is there a more efficient way? As these seem to slow everything down Formulas below are based on text data in column A, and values in Column B ​Lookup Column C =LOOKUP(2,1/($A$1:A2=A2),$B$1:B2) Someone on Reddit gave me this...
  11. I

    IF Unique line

    Hi 'Alternative to vba Code If pos = 2 Then pos = 3 If pos = 5 Then pos = 6 If pos = 34 Then pos = 35 Thanks
  12. C

    Alternative to Distinct Count in Pivot Tables?

    Good Morning, There is a user who has the Mac version of Excel which doesn't include the option for Distinct Count in Pivot Tables. Is there an alternative method to get the same result? Many thanks in advance
  13. E

    Alternative Formula

    Good evening, Would it be possible to have an alternative formula to the following as this only returns data if D12 is a alpha numeric value =IFERROR(LOOKUP(2,1/(Data!$AK$3:$AK$502=D12)/(Data!$AD$3:$AD$502=F12),(Data!$X$3:$X$502)),"") this matched D12 within AK3:AK502 and F12 within AD3:AD502...
  14. M

    Sendkeys not working Alternative code?

    I am trying to refresh and factset excel template with vba, I am trying to use sendkeys to use the hot keys that will kick off the refresh. The code I am using is this Application.SendKeys ("%srw") which works by itself but not with other code. ( I am cycling through portfolios and getting...
  15. X

    Excel 2016 MAC .onkey for Command+V alternative

    As i have gone through almost entire internet and couldn't find a solution to address 'Command+V' for .onKey procedure in Excel VBA. Is there any alternative?
  16. H

    =sumproduct(averageifs(...

    Hi, I regularly use sumproduct(countifs( or sumproduct(sumifs where one of the criteria is a range and it works great.. However I am attempting to do this with averageifs and it doesnt work.. is there an alternative? or something I am missing something? example of what im trying to make...
  17. S

    Alternative to print2excel

    Hi, I was wondering if anyone here knew of an alternative to the Print2Excel software which has been retired and no longer for sale: http://print2excel.com/ I have a client that was using the free version but would like to purchase the paid version but is, obviously, not able to do so...
  18. R

    Lookup Name in Multiple Fields

    Hello, I am working on a spreadsheet that has the following setup. What would be the best way to find out which projects each person is on? I was thinking of doing a vlookup, but I would like to know if there is a better alternative. <tbody> Project Resource 1 Resource 2 Resource 3...
  19. E

    Alternatives database

    Dear, Using excel, I want to create data base that can train my employees to give alternative products to clients....in other words, if my employee wants to sell a blue pen and he didn't find it in store, the database will help him to give alternative options to sell instead with picture to...
  20. O

    Alternative to INDIRECT to SUMIF in multiple worksheets

    Hi Community, I am using Excel 2010. I have a problem with the formula below because it makes the workbook VERY slow: =IF(B$2="",0,SUMPRODUCT(SUMIF(INDIRECT("'"&Tabs&"'!B25:B245"),B$2,INDIRECT("'"&Tabs&"'!L25:L245")))) I am trying to do a SUMIF in 80 worksheets (named range 'Tabs') and the...

Some videos you may like

This Week's Hot Topics

Top