Search results

Hi, If you have the latest Excel version, the task is quite easy in that with formulas, otherwise single formula will be quite complicated as suggested above by @XOR LX Regards,

Hi, Another formula solution: Use below layout Formula in B2 and copy across and down =SUMPRODUCT(--(Sheet1!$B$2:$B$355=Sheet2!B$1),INDEX(Sheet1!$C$2:$E$355,,MATCH(Sheet2!$A2,Sheet1!$C$1:$E$1,0))) Regards,

I had already attached my solution in my last reply. Please refer it. Regards,

Hi, I would also suggest you to download Power Pivot, it is available for Excel 2013 and above, and for Excel 2010, use below link to download it. It's free and quite useful. https://www.microsoft.com/en-in/download/details.aspx?id=102 Alternatively, if you keep security data separate, you...

Hi, Your question statement has some problem, if you want to sum of all the years (let say for the month Jan) than what good is the use of year filter. Although you can use below file, it uses Microsoft 365 latest formulas. Regards,

Hi, Just another formula. =SUMPRODUCT(--($D$7=$D$14:$T$14)*$D$16:$T$43) Regards,

Hi, Just another solution using NETWORKDAYS.INTL function. See the attached file. Regards,

Hi, Just a passing thought, isn't Pivot can solve the problem here very easily. Until unless the question is about learning advanced formulas. Regards, Somendra

Hi, Use below formula on Placement Sheet in A2 and copy down and across. =INDEX(DataSet!$A$2:$D$4,ROWS(A$2:A2),MATCH('Placement Sheet'!A$1,DataSet!$A$1:$D$1,0)) Regards,

I started my journey with Lotus 1-2-3 (creating a graph in it was supercool in those days). Now all that seems so easy with Power Bi and other tools. dBase was also the same time.... it was fun to work on those 80286 system with 2Mb RAM, 40MB Harddisk.....

Hi, Do you want to add both column Y and N against one country? For eg: South Africa --> Total Y in column D is 11 and in column E is 9. So in Summary Sheet against South Africa you want 20 as result in Y? Regards,

Hi, Use in D2 and drag down. =IF(OR(AND(B2>=6,C2>=50000),B2>=12,C2>=100000),"Yes","No") Regards,

Hi, Based on your sample file, see I had created below formulas. Formula used for GREEN: =AND($B2>=1,$B2<=3) for rest you can modify the conditions. Regards,

Hi, Just a different solution. I will make a table of countries, as it is fixed and does not change very often, as shown below. I will give this table a name such as CountryList. Than I will use below formula to extract the country name first...

Hi, Text formulas are tricky some time and depends heavily on the input data. The format and pattern of data to be uniform across all cells to get consistent results. If the data in your sample is consistent than you can use below formula. For Root Cause : =MID(C4,FIND($D$3&"...

Hi, Your this requirement is different from your earlier requirement. If I remember correctly, your earlier requirement is to fetch date and time based on year in E1 cell. But now you are asking to make a summary sheet based on all the years. Regards,

HI, For formula for percentage use something like this =SUMPRODUCT(--($C$15:$C$22=1),--(MONTH(F15:F22)=3),--(H15:H22="Met"))/SUMPRODUCT(--(MONTH($F$15:$F$22)=3)) Note: the formula is hard coded with month number as 3. Regards,

@BJ576 I did not get you point, it pulling out data based on the year in cell E1 of Jobs sheet. What exactly you need. yo do not have any data on other sheets. In a blank case it will just return 1st day of the year and first hour of the day. I think you asked formula in yellow cells at...

Hi, Just another formula..... =SUMPRODUCT((COUNTIF($L$2:$L$4,$B$2:$J$2)=0)*(B3:J3="")) Regards,

Hi, See this file. Regards,

Thanks, for your feedback. Regards.

Go to B2 cell. Press F2..Ctrl +V to paste the formula. Than Hold Ctrl key, Shift Key together and press enter. Once you get result drag it to other cells. Regards.

Hi, Here my take... use below formula on summary sheet B2 cell and copy across and down. =SUM(IF(IF(B$1=List!$B$2:$B$11,List!$A$2:$A$11)=TRANSPOSE(Data!$B$1:$K$1),TRANSPOSE(Data!$B2:$K2))) As this is an array formula use Ctrl+Shift+Enter to enter the formula. Regards.

@jesalmehta @DaveUnr ... Thanks for writing. Basically, when you import data from other sources...many times the blank looking cells are not blanks exactly. They contain data which Excel treats as data...but it looks blank to us, that's why excel jump to last cell when you press Ctrl+down...

Hi, Another Option: Put in B62 below formula. Regards.