Search results

Hi, This is one of the best guides around http://www.xldynamic.com/source/xld.SUMPRODUCT.html

VLOOKUP does not need the data to be sorted if you use false as the last parameter. It has the limitation that only colums to the left of the referred item can be 'looked up' Index and match is more flexible but IMO takes a bit more learning. Shades, nice to see you are still around and...

Hi Tony, You can do this by formula e.g. in B1 =A1&" "&A2 Leave B2 blank In B3=A3&" "&A4 leave B4 Blank Now select B1 to B4 Copy down as far as you need

Try this. I have altered the layout a bit and created a pricelist to look up the values

Suggest that you use Vlookup to add in the various components if a Yes exists. If you post an example of your workbook it would be easier to advise.....

The data sources must be on the same sheet as far as I am aware. If you think of the logic, it looks at one set of data and counts those which meet the criteria you specify - it can't look at two separate tables of data, that would be meaningless.

Try taking the " " out from the numbers. There appears to be a problem with the formatting of col F. If you set the format to number and re-enter them it works. I think you have a problem in your formula consruct as well, if I understand your intention right...

You can do this using Vlookup see the attached example Ed

I agree it's it not pretty - another possible way round could be using a VLOOKUP table but, without knowing the data format, one can't be sure. In general, I find that there's usually a way to avoid huge nested IFs. Regards

Glad you got it working. Thanks for the feedback. Regards

yes it is possible, check out this site http://www.cpearson.com/excel/nested.htm Regards

You need to use VLOOKUP - if you post a sample of your file in ZIP form it will make it easier to help you.

Hi Jon, Sounds like you need to use VLOOKUP to populate the cells - can you attach a sample sheet so that we can better advise you?

Hi Paul, Did you try the formula I posted ealier? It should do the trick =SUMIF($B$3:$B$50,B59,$BW$3:$BW$50) And copy down Edited to put in absolute refs

No probs, Ken. Howabout =SUMIF(B3:B50,B59,BW3:BW50) Ed

Hi Paul, Could you post your sheet, or a part of it, with an explanation of what you want to achieve? Ed

Yes, it can be done but needs VBA. Beyond my skill level - if you get no joy here suggest you post in http://www.excelforum.com/ or http://www.ozgrid.com/forum/forumdisplay.php?f=8 Remember to include a reference to your original thread here Regards Ed

Glad to have helped and thanks for the feedback. If you select a cell reference in the formula bar, press F4 and it will cycle through the possibilities by repeated F4 pressing. Ed

Is the layout the same in the destination workbook as the source? If so once you have got your first cell entered, remove the dollar signs and then by right clicking and holding on the little + sign on the bottom RHS you can copy over the range you want. HTH Ed

No problem. Thanks for the feedback Ed