When I have a pivot table it has certain numbers I want to grab. If I link to a cell within the pivot table it uses text to search for the data in the pivot table. A2 =GETPIVOTDATA("P/S",Sales!$A$3,"Sales Categories","Range < 50")

Rather than use "Range < 50" I want to grab text from another cell (say B2= "Range < 40") to make the model more dynamic. However the get formula searches by text and not the cell link. What I want is something like this A2 =GETPIVOTDATA("P/S",Sales!$A$3,"Sales Categories",&B2) but unfortunately this does not work.

Do you know how to get around this issue?

Thank you.

Formula for the keys or short curt for excel

Posted on Jul 28, 2008

This problem arose from Excel Verson 2003 onwards - It does not appear in a sum function where the range is greater than one cell.

The easiest solution is to manually linking the cell in your formula and typing in the cell reference rather than looking it up

Posted on May 25, 2009

Sum

person tege

max

Posted on Jul 22, 2008

Formulas are used to specify calculations based on values in designated cells. Excel supports basic calculations as well as statistical, trigonometric and other specialized functions.

Formulas used in Excel must follow a certain syntax.

- All formulas begin with an equals sign
**(=)**. - Some formulas use operands such as
**+,-, *,/**for addition, subtraction, multiplication or division.

For example, the formula =A1+A2+A3 would add the contents of cells A1, A2 and A3. - Other formulas refer to different functions such as SUM, AVERAGE and others.

For example, the formula =SUM(A1:A3) would add the contents for the range A1 through A3. - Formulas can be
**combined with operands.**

For example, the formula =10*SUM(A1:A3) would add the contents cells A1 through A3 and multiply them by 10. - Functions can
**be nested within each other.**

For example, the formula =SQRT(10*SUM(A1:A3)) would take the square root of ten times the sum of cells A1 through A3. When functions are nested, it is important that the number of left parentheses match the number of right parentheses.

Aug 19, 2011 | Microsoft EXCEL 2004 for Mac

In the cell that you
want the result to appear in, enter the appropriate formula from the following
examples.

How to Count the Occurrences of a Number

Use this formula

=SUM(IF(range=number,1,0))

where range is the range that you want to search, and number is the number that you want to count.

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

How to Count the Occurrences of a Text String

Method 1

Use this formula

=SUM(IF(range="text",1,0))

where range is the range that you want to search, and text is the text that you want to find (the text must be enclosed in quotation marks).

NOTE: The above formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Method 2

Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula

=COUNTIF(range,"text")

where range is the range of cells that you are evaluating, and text is the text string that you want to count instances of (note that text must be enclosed in quotation marks).

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Wildcard characters can be used within the COUNTIF function.

The asterisk character (*) represents more than one character. For example, to count all the cells in the range a1:a10 that contain an "x," you can use the following formula:

=COUNTIF(a1:a10,"*x*")

The question mark character (?) can also be used to represent one wildcard character -- for example, to count all cells in the range whose second character is the letter, such as "ax" or "bx."

=COUNTIF(a1:a10,"?x*")

How to Count the Occurrences of a Number

Use this formula

=SUM(IF(range=number,1,0))

where range is the range that you want to search, and number is the number that you want to count.

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

How to Count the Occurrences of a Text String

Method 1

Use this formula

=SUM(IF(range="text",1,0))

where range is the range that you want to search, and text is the text that you want to find (the text must be enclosed in quotation marks).

NOTE: The above formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Method 2

Use the COUNTIF() function to count the occurrences of a text string. For example, use the formula

=COUNTIF(range,"text")

where range is the range of cells that you are evaluating, and text is the text string that you want to count instances of (note that text must be enclosed in quotation marks).

NOTE: This formula must be entered as an array formula. To enter an array formula, press CTRL+SHIFT+ENTER.

Wildcard characters can be used within the COUNTIF function.

The asterisk character (*) represents more than one character. For example, to count all the cells in the range a1:a10 that contain an "x," you can use the following formula:

=COUNTIF(a1:a10,"*x*")

The question mark character (?) can also be used to represent one wildcard character -- for example, to count all cells in the range whose second character is the letter, such as "ax" or "bx."

=COUNTIF(a1:a10,"?x*")

on Nov 11, 2013 | Microsoft Excel Business & Productivity...

Hi, Not sure if this is what you have tried:

1: Assuming each run is on a different column

Run1 Run2 Run3 Run4, etc...and the maxium lenght of each column is 9 values, but each colum has a different lenght of numbers..

Then you can use:

=MAX(COUNTIF(A2:A10,"<>0"),COUNTIF(B2:B10,"<>0"), {keep adding countif for each colum).

If all runs are on the same colum, then using Pivot tables is easier. If you have a copy of your data, please post it so that I can give you the exact formula ;)

1: Assuming each run is on a different column

Run1 Run2 Run3 Run4, etc...and the maxium lenght of each column is 9 values, but each colum has a different lenght of numbers..

Then you can use:

=MAX(COUNTIF(A2:A10,"<>0"),COUNTIF(B2:B10,"<>0"), {keep adding countif for each colum).

If all runs are on the same colum, then using Pivot tables is easier. If you have a copy of your data, please post it so that I can give you the exact formula ;)

Oct 21, 2009 | Microsoft Excel for PC

Use this link for pivit tables

http://office.microsoft.com/en-us/excel/HA010346321033.aspx

Use this link for Mail Merge:

http://www.excel-tutorials.org/worksheets/how-to-create-a-mail-merge-using-microsoft-excel-and-word/

http://office.microsoft.com/en-us/excel/HA010346321033.aspx

Use this link for Mail Merge:

http://www.excel-tutorials.org/worksheets/how-to-create-a-mail-merge-using-microsoft-excel-and-word/

Feb 27, 2009 | Microsoft Excel for PC

You can use this formula

=IF(A2<=100,"Within budget","Over budget")

Which means

If the number above is less than or equal to 100, then the formula displays "Within budget". Otherwise, the function displays "Over budget" (Within budget)

or you and try something like this

=IF(A2=100,SUM(B5:B15),"")

which means

If the number above is 100, then the range B5:B15 is calculated. Otherwise, empty text ("") is returned ()

I got these examples from the help within Exel they give several more examples and more expaination.

=IF(A2<=100,"Within budget","Over budget")

Which means

If the number above is less than or equal to 100, then the formula displays "Within budget". Otherwise, the function displays "Over budget" (Within budget)

or you and try something like this

=IF(A2=100,SUM(B5:B15),"")

which means

If the number above is 100, then the range B5:B15 is calculated. Otherwise, empty text ("") is returned ()

I got these examples from the help within Exel they give several more examples and more expaination.

Jan 10, 2009 | Microsoft Office Standard for PC

Check out: Tools -> Data -> Filter -> Auto Filter.

You may want to look at the help function to get a better idea of how powerful this feature is.

Let me know if this helps.

You may want to look at the help function to get a better idea of how powerful this feature is.

Let me know if this helps.

May 08, 2008 | Microsoft Excel for PC

You need to create subtotals so that it looks like something like this:

Prod/units Amt. Level Total

xxxx 10.00 1

yyyy 20.00 1

30.00

dddd 40.00 2

ffff 30.00 2

70.00

etc.

To do this I would suggest using the Pivot Table feature of Excel. It is not too hard to use but you do have to know what you want and also have to be sure you are getting results as expected. Like any tool, it has to be used right. You can also use sub-total functions supplied in VBA as well in your cells.

If you want to avoid using VBA functions in cell and deal with ranges, etc., use the Pivot Table Wizard.

Some reading:

http://www.ehow.com/how_13020_pivot-table-wizard.html

http://www.microsoft.com/dynamics/using/excel_pivot_tables_collins.mspx

http://www.ozgrid.com/Excel/excel-pivot-tables.htm

Also Google "Excel pivot table wizard" to get more.

Good luck!

Prod/units Amt. Level Total

xxxx 10.00 1

yyyy 20.00 1

30.00

dddd 40.00 2

ffff 30.00 2

70.00

etc.

To do this I would suggest using the Pivot Table feature of Excel. It is not too hard to use but you do have to know what you want and also have to be sure you are getting results as expected. Like any tool, it has to be used right. You can also use sub-total functions supplied in VBA as well in your cells.

If you want to avoid using VBA functions in cell and deal with ranges, etc., use the Pivot Table Wizard.

Some reading:

http://www.ehow.com/how_13020_pivot-table-wizard.html

http://www.microsoft.com/dynamics/using/excel_pivot_tables_collins.mspx

http://www.ozgrid.com/Excel/excel-pivot-tables.htm

Also Google "Excel pivot table wizard" to get more.

Good luck!

Feb 23, 2008 | Microsoft Office Standard for PC

If you can move your name column (C) to the first column, you could leverage the VLOOKUP formula pretty easily.

To do this, do the following:

1) Move the C Column to be the A Column, shifting all other columns to the right.

2) (optional) Insert a new row at the top of the sheet (to hold the formula & seach value)

3) Use A1 as your search field.

4) In A2, enter the following formula:

=VLOOKUP($A$1,$A$2:$C$6,3,)

Describing above parameters, in the formula:

$A$1 -> the search field (name your looking for).

$A$2:$C$6 -> The table/grid you wish to search and return values from. The left most column (A) must contain the values to be searched.

3 -> is the column number (A=1,B=2,C=3, etc) within the table/grid to return.

If you cannot make the name column your first (A) column, there are more complex ways to do this. For instance, create a new sheet which redisplays the info in the structure easier for this method, and perform the VLOOKUP on that data. Other options might exist in creating a complex formula that would get you what you want.

Also, if you can sort column A (names) it would find results faster, if your data set is large.

To do this, do the following:

1) Move the C Column to be the A Column, shifting all other columns to the right.

2) (optional) Insert a new row at the top of the sheet (to hold the formula & seach value)

3) Use A1 as your search field.

4) In A2, enter the following formula:

=VLOOKUP($A$1,$A$2:$C$6,3,)

Describing above parameters, in the formula:

$A$1 -> the search field (name your looking for).

$A$2:$C$6 -> The table/grid you wish to search and return values from. The left most column (A) must contain the values to be searched.

3 -> is the column number (A=1,B=2,C=3, etc) within the table/grid to return.

If you cannot make the name column your first (A) column, there are more complex ways to do this. For instance, create a new sheet which redisplays the info in the structure easier for this method, and perform the VLOOKUP on that data. Other options might exist in creating a complex formula that would get you what you want.

Also, if you can sort column A (names) it would find results faster, if your data set is large.

Feb 03, 2008 | Microsoft Excel for PC

Make sure that the cell is formatted as a number, not as text. (Right-click the cell, format cell, number, choose NUMBER.)

Dec 19, 2007 | Microsoft Excel for PC

Yes of course, if you have a little background of visual basic programming that's possible you can enhance your requirements.

Please see link http://www.techonthenet.com/excel/questions/cond_format1.php

Please see link http://www.techonthenet.com/excel/questions/cond_format1.php

Nov 29, 2007 | Microsoft Excel for PC

Jan 28, 2016 | Microsoft Excel for PC

154 people viewed this question

Usually answered in minutes!

×