Search results

The conditions of SUMIFS must all be met. Since in your data, no row is in East, West, South and North regions it gives zero. To get what you want, use this instead: =SUMIFS(sales,region,"east") + SUMIFS(sales,region,"west") + SUMIFS(sales,region,"north") + SUMIFS(sales,region,"south")

I think there is no simple way to find out which row an object (button, text box or shape) sits in. Objects do not really belong to any cells, rather they float on cell lay out. So you only object.top, object.left. There is a way to figure out the row number from object.top by looping thru...

Interesting question. You need to deduplicate your data in column W first to get this work. I say in column X, write something like this: =W2+(0.0000000001)*row() and fill it down. And now use this in place of your array formula and drag it down. Assuming you are writing this formula in...

You can do it many ways, the easiest I can find is below. 1. Go to sheet 2, select all the invalid mailids and give them a name as list2 2. Come back to sheet 1 and in the adjacent column write =MATCH(A1,list2,0) 3. Drag it down to fill this for all 2000 email ids 4. Now select this results...

Select the slicer, from slicer settings ribbon adjust columns to 3 or 4.

This probably has very little to do with your computer configuration and more to do with your macro code. To begin with, I suggest following checks: Break your macro in to smaller sub macros and run one after another If you have a loop that processes lots of rows, try running it for few...

Welcome to Chandoo.org forums and thanks for your question. Please take time to explore our forums to learn better. Read the 3 sticky threads on front page too. Regarding your question, assuming the DP id is in A1 & your data in A4:D10 write =VLOOKUP(A1,$A$4:$D$10,2,false) &...

Interesting question GB. I do this often for my bank statements too. But never used formulas as my statement is even more complicated. But in your case, it makes good sense. You can use SUMPRODUCT, LEFT, LEN etc. to check which "Start with" matches with a given transaction and replace the...

Your vlookup is returning #REF because you are using only column for lookup area, where you want information from 5th column. Make this change and it should work: =VLOOKUP($A2,B_EOM!$A$10:$E$227,5,FALSE) If you want to use both first name and last name in the lookup, use this array formula...

Sounds like a fun idea RK... lets see what our users come up with. I will go first. 1. Most interesting formula. Well, there are so many formulas I wrote that meet this criteria. One of them is to calculate Work break down structure recursively using INDEX etc. You can see a similar formula...

Hi Unknown8 Welcome once again. We would be very glad if you do not use our forum for academic assignments :)

Excellent work Sajan :) Thanks for sharing this solution.

Interesting question. I could not come up with a quick solution with formulas alone, so VBA to the rescue. The basic logic is simple, 1. Have two tables one for dues, one for payments. 2. For each payment, we will go thru rows in dues and see 3. if customer matches, and whether can...

Thanks for your question Weow.. I have looked at the file and I could not understand why this error is happening either. When I evaluate portions of the formula, I get correct result. I think the error may be due to complicated nature of your formulas. Since it felt like too much work to go...

If you do not want to clean your source data, you can also try using *vlookup value, like this: =vlookup("*"&value-to-look, range, column, false) This will ignore any spaces. For more on using such wildcards, refer to http://chandoo.org/wp/2010/11/01/using-wildcards-with-vlookup/

KPJ.. this uses Data validation > Input message feature. To set up, select a cell, go to data validation and add input message. This will show up whenever user clicks on that input cell.

Hi Renie, I think I know what the problem is. You must be entering the dates 10-sept-2012 in the gantt chart Start day column (C). You need to enter the day number - ie 1 (since project start date is also 10-sept-2012). And enter the duration as 17 (since there are 17 days between 10-sept and...

Hi John... As you may know, time is nothing but a value between 0 to 1. Each day in Excel is 1 number, so each hour is 1/24. This 6:00 hrs would be 0.25. So if a dentist logged 93:00 hrs, it would be 93/24 = 3.88. Since 3.88 can be represented as a date, Excel correctly shows it as 3/01/1900...

I am going out of my way here, but let me say this. Whatever nonsense Tom ford han & Isabel Marant make suck. Stop spamming us with your boring tags :P

Hi Ashl... see this for a start http://chandoo.org/wp/2008/08/20/create-kpi-dashboards-excel-1/ You can replace OFFSET with INDEX to get same result.

Hi Collock, Not just some functions, practically all functions in Excel do not update references when you sort the data. A reference A1 written in B1 just refers to one cell to the left. It does not matter what value the cell contains due to sorting. Please read...

@Xljgd: As you Hui mentioned, you should come up with a formula that mimics exact conditions set in conditional formatting to count highlighted cells. For your original example, you are highlighting all dates prior to TODAY(). So, to count number of cells highlighted, you can use below...

Love your new avatar Faseeh...

Many congratulations Hui.. A huge achievement indeed...

Many congratulations Faseeh. Thanks for your excellent tips & answers. Wishing you many more milestones... :)