Quick Links ▼

Bookmark page

Printable version

Customer: Sign in
Delegate: Sign in
Trainer: Log in

Forum home » Delegate support and help forum » Microsoft Excel Training and help » SumProduct I believe

	98.1 % recommend us
	More live stats...

The UK's most regular
instructor-led training courses.

Training information:

SumProduct I believe

Resolved · Urgent Priority · Version 2003

Reply Wed 30 Mar 2011, 16:39Estefania (guest) said...

SumProduct I believe

Hello,

I require help on a formula. I want the formula to do the following:

When excel finds this condition: CONCATENATE('Summary Trade Spend'!A7,"TY") in the range D:X I need it to count the number of cells that have a value in them in column X:X.

Let me explain this another way if that wasn't clear enough:

When a product name has a TY next to it I want my formula to count how many cells have values in them for those columns in a different set of columns.

All of the data for the above is in one spreadsheet, and the forumla result is in another. I have been told that SUMPRODUCT might be the answer, but only might. I dont quite understand the the sumproduct formula.

Any help would be graetly appreciated.

Thank you.

Este

For upcoming training course dates see: Pricing & availability

Reply Thu 31 Mar 2011, 18:22Trainer Anthony said...

RE: SumProduct I believe

Hi Este, thanks for your query. Can you clarify why you need to count the values in column X every time you find CONCATENATE('Summary Trade Spend'!A7,"TY") somewhere else? Can't you just count the number of instances of any text suffixed with "TY"? (e.g. =COUNTIF(D:X, "*TY"))

Anthony

Thu 7 Apr 2011: Automatically marked as resolved.

Excel articles

· Importing Useful Information From The Web To Excel
· Microsoft Excel Courses Teach Advanced Macro Techniques
· Say It With Ribbon
· MS Excel: Excel At Making The Most Of Your Christmas
· How Small Businesses Profit from Outside Excel Training

Please browse our web site to find out more about
excel+courses+in+london and other Microsoft training courses.

Excel tip:

Hide columns in an Excel 2010 Worksheet

If you don’t want part of the Excel worksheet to be visible or when you don’t want certain data to appear in print outs, then a simple solution is to temporarily hide a column or multiple columns.

Hiding a single column:

1)Right click on the column header of the column you want to hide (this is the grey bar along the top edge of the worksheet)
2)Choose Hide from the menu
3)This column will now be hidden from view

Hide more than one column:

1)In the column header drag select to highlight the columns you want hidden
2)Right click and choose Hide from the menu

View all Excel hints and tips