|
| If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|||||||
|
|
Thread Tools | Display Modes |
|
#1
June 26th, 2006, 05:14 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
SumIf query ...
Does this function ignore invisible cells (autofilter) ? If not, what could be an alternative? -- muchacho ------------------------------------------------------------------------ muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082 View this thread: http://www.excelforum.com/showthread...hreadid=555662 
|
|
#2
June 26th, 2006, 05:24 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf query ...
use subtotal(9,range)
on the sunif to ignore hides rows HTH regards from Brazil Marcelo "muchacho" escreveu: Does this function ignore invisible cells (autofilter) ? If not, what could be an alternative? -- muchacho ------------------------------------------------------------------------ muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082 View this thread: http://www.excelforum.com/showthread...hreadid=555662
|
|
#3
June 26th, 2006, 05:29 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf query ...
SORRY IT'S DOESN'T WORK
MARCELO "Marcelo" escreveu: use subtotal(9,range) on the sunif to ignore hides rows HTH regards from Brazil Marcelo "muchacho" escreveu: Does this function ignore invisible cells (autofilter) ? If not, what could be an alternative? -- muchacho ------------------------------------------------------------------------ muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082 View this thread: http://www.excelforum.com/showthread...hreadid=555662
|
|
#4
June 26th, 2006, 05:53 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf query ...
what makes you think it doesn't?
-- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Marcelo" wrote in message ... SORRY IT'S DOESN'T WORK MARCELO "Marcelo" escreveu: use subtotal(9,range) on the sunif to ignore hides rows HTH regards from Brazil Marcelo "muchacho" escreveu: Does this function ignore invisible cells (autofilter) ? If not, what could be an alternative? -- muchacho ------------------------------------------------------------------------ muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082 View this thread: http://www.excelforum.com/showthread...hreadid=555662
|
|
#5
June 26th, 2006, 06:03 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf query ...
Hi Bob,
I have type without test, and when I have tried it doesn't work here, course I did something bad, if you say it works. It works. Regards and thanks for the feedback Marcelo "Bob Phillips" escreveu: what makes you think it doesn't? -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "Marcelo" wrote in message ... SORRY IT'S DOESN'T WORK MARCELO "Marcelo" escreveu: use subtotal(9,range) on the sunif to ignore hides rows HTH regards from Brazil Marcelo "muchacho" escreveu: Does this function ignore invisible cells (autofilter) ? If not, what could be an alternative? -- muchacho ------------------------------------------------------------------------ muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082 View this thread: http://www.excelforum.com/showthread...hreadid=555662
|
|
#6
June 26th, 2006, 06:10 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf query ...
Surely I need an IF statement though. What I want is for it to add some numbers up but only if the cell equals X. For example ... Position - Value 2 - 33 2 - 44 4 - 66 5 - 33 6 - 22 So here I would like to display the total of position 2. Which would be 33+44. I use autofilter so I only want it to work with the numbers in view. I'm wondering what I would use to have the SubTotal mixed with an IF statement. -- muchacho ------------------------------------------------------------------------ muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082 View this thread: http://www.excelforum.com/showthread...hreadid=555662
|
|
#7
June 26th, 2006, 06:31 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf query ...
=subtotal(9,range)
HTH regards Marcelo "muchacho" escreveu: Surely I need an IF statement though. What I want is for it to add some numbers up but only if the cell equals X. For example ... Position - Value 2 - 33 2 - 44 4 - 66 5 - 33 6 - 22 So here I would like to display the total of position 2. Which would be 33+44. I use autofilter so I only want it to work with the numbers in view. I'm wondering what I would use to have the SubTotal mixed with an IF statement. -- muchacho ------------------------------------------------------------------------ muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082 View this thread: http://www.excelforum.com/showthread...hreadid=555662
|
|
#8
June 26th, 2006, 06:38 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf query ...
That is different, introducing a condition.
Try this =SUMPRODUCT(SUBTOTAL(9,OFFSET($B$1,ROW($B$1:$B$200 )-ROW($B$1),,1))*(A1:A200= "X")) -- HTH Bob Phillips (replace xxxx in the email address with gmail if mailing direct) "muchacho" wrote in message ... Surely I need an IF statement though. What I want is for it to add some numbers up but only if the cell equals X. For example ... Position - Value 2 - 33 2 - 44 4 - 66 5 - 33 6 - 22 So here I would like to display the total of position 2. Which would be 33+44. I use autofilter so I only want it to work with the numbers in view. I'm wondering what I would use to have the SubTotal mixed with an IF statement. -- muchacho ------------------------------------------------------------------------ muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082 View this thread: http://www.excelforum.com/showthread...hreadid=555662
|
|
#9
June 26th, 2006, 06:42 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf query ...
No no no, this will add ALL the visible values up. Position - Word - Value 3 - Hot- 45 5 - Hot - 23 5 - Hot - 32 7 - Hot - 44 2 - Hot - 22 Ok, now in this example, I'd only want to calculate the value of HOT when it was in 5th position. I need an IF statement somewhere. SumIf would include the invisible cells as well wouldn't it? I need something like Add totals for the word HOT (or whichever word I've filtered out to) in position 5. -- muchacho ------------------------------------------------------------------------ muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082 View this thread: http://www.excelforum.com/showthread...hreadid=555662
|
|
#10
June 26th, 2006, 06:49 PM
posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
|
SumIf query ...
Hi Bob, Can you talk me through the function you just gave to me ... what's it doing? -- muchacho ------------------------------------------------------------------------ muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082 View this thread: http://www.excelforum.com/showthread...hreadid=555662
|
| Thread Tools | |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Thread Starter | Forum | Replies | Last Post |
| Advanced SQL Query | Wes Henry | General Discussion | 3 | December 14th, 2005 09:55 PM |
| Toolbars, Drop-Down Menus | Rick | New Users | 1 | September 21st, 2005 11:17 AM |
| SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
| Nested in-line Query | laura | Running & Setting Up Queries | 0 | February 11th, 2005 12:17 AM |
| Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Linear Mode
