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

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

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

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

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

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

=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

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

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

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

I tried the above but couldn't get it to work.


Column C is where my keywords are
Column E is position.
Column G is the number of clicks that keyword has received



Now, say I filter out C for the keyword 'test' I then want it to
display the total amount of clicks (column G) when it's in position 1
(1st).

Can anybody think of a formula I can try?

The formula is on a different worksheet.


--
muchacho
------------------------------------------------------------------------
muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082
View this thread: http://www.excelforum.com/showthread...hreadid=555662

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Sheet2!$G$1,ROW(Shee t2!$G$1:$G$200)-ROW(Sheet2
!$G$1),,1))*
(Sheet2!$E$1:$E$200=1))

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"muchacho" wrote in
message ...

I tried the above but couldn't get it to work.


Column C is where my keywords are
Column E is position.
Column G is the number of clicks that keyword has received



Now, say I filter out C for the keyword 'test' I then want it to
display the total amount of clicks (column G) when it's in position 1
(1st).

Can anybody think of a formula I can try?

The formula is on a different worksheet.


--
muchacho
------------------------------------------------------------------------
muchacho's Profile:
http://www.excelforum.com/member.php...o&userid=35082
View this thread: http://www.excelforum.com/showthread...hreadid=555662

I tried it but just can't get it to work.

If anybody would be happy with downloading the file and taking a look
at it ...
http://www.realearners.biz/stats.xls


I've only got a few words in, as the original is over 6mb.


--
muchacho
------------------------------------------------------------------------
muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082
View this thread: http://www.excelforum.com/showthread...hreadid=555662

I have a solution but I am failing to upload it, will try again tomorrow
morning . I changed your data a bit, to better show it working.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"muchacho" wrote in
message ...

I tried it but just can't get it to work.

If anybody would be happy with downloading the file and taking a look
at it ...
http://www.realearners.biz/stats.xls


I've only got a few words in, as the original is over 6mb.


--
muchacho
------------------------------------------------------------------------
muchacho's Profile:
http://www.excelforum.com/member.php...o&userid=35082
View this thread: http://www.excelforum.com/showthread...hreadid=555662

OK, cheers Bob, I'll wait for further notice.


--
muchacho
------------------------------------------------------------------------
muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082
View this thread: http://www.excelforum.com/showthread...hreadid=555662

I couldn't use my favourite temporary file server, so I loaded it here

http://xldynamic.com/example%20code/...0-%20stats.xls

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"muchacho" wrote in
message ...

OK, cheers Bob, I'll wait for further notice.


--
muchacho
------------------------------------------------------------------------
muchacho's Profile:
http://www.excelforum.com/member.php...o&userid=35082
View this thread: http://www.excelforum.com/showthread...hreadid=555662

Thanks for the help.

So what would I need to change if I have like 40,000 rows of data? (not
just the 3 when you looked at it).

I tried changing the $G$1:$G$20 to $G$1:$G$40000 and it displayed the
#N/A sign.


--
muchacho
------------------------------------------------------------------------
muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082
View this thread: http://www.excelforum.com/showthread...hreadid=555662

You have to keep ranges the same size, so if you change one, you need to
change the other commensurately.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"muchacho" wrote in
message ...

Thanks for the help.

So what would I need to change if I have like 40,000 rows of data? (not
just the 3 when you looked at it).

I tried changing the $G$1:$G$20 to $G$1:$G$40000 and it displayed the
#N/A sign.


--
muchacho
------------------------------------------------------------------------
muchacho's Profile:
http://www.excelforum.com/member.php...o&userid=35082
View this thread: http://www.excelforum.com/showthread...hreadid=555662

Any idea why, if I copy and paste data into the spreadsheet the formula
doesn't work at just brings up zeros?


--
muchacho
------------------------------------------------------------------------
muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082
View this thread: http://www.excelforum.com/showthread...hreadid=555662

Not without seeing it.

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"muchacho" wrote in
message ...

Any idea why, if I copy and paste data into the spreadsheet the formula
doesn't work at just brings up zeros?


--
muchacho
------------------------------------------------------------------------
muchacho's Profile:
http://www.excelforum.com/member.php...o&userid=35082
View this thread: http://www.excelforum.com/showthread...hreadid=555662

Hi Bob, could you please take a look, i've uploaded my sheet again:

http://www.realearners.biz/stats2.xls

Many thanks.


--
muchacho
------------------------------------------------------------------------
muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082
View this thread: http://www.excelforum.com/showthread...hreadid=555662

Just bumping the post.


--
muchacho
------------------------------------------------------------------------
muchacho's Profile: http://www.excelforum.com/member.php...o&userid=35082
View this thread: http://www.excelforum.com/showthread...hreadid=555662