SumIf query ...

Does this function ignore invisible cells (autofilter) ?

If not, what could be an alternative?

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" <Marcelo@discussions.microsoft.com> wrote in message
news:F3F7748B-8E54-41B9-AEEB-DDF0402EB850@microsoft.com...
> 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" <Marcelo@discussions.microsoft.com> wrote in message
> news:F3F7748B-8E54-41B9-AEEB-DDF0402EB850@microsoft.com...
> > 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.

=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
>
>

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.

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" <muchacho.2a0rmz_1151342106.3035@excelforum-nospam.com> wrote in
message news:muchacho.2a0rmz_1151342106.3035@excelforum-nospam.com...
>
> 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
>

Hi Bob,

Can you talk me through the function you just gave to me ... what's it doing?

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.

=SUMPRODUCT(SUBTOTAL(9,OFFSET(Sheet2!$G$1,ROW(Sheet2!$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" <muchacho.2a0vkq_1151347206.4747@excelforum-nospam.com> wrote in
message news:muchacho.2a0vkq_1151347206.4747@excelforum-nospam.com...
>
> 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.

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" <muchacho.2a0zz0_1151352907.4377@excelforum-nospam.com> wrote in
message news:muchacho.2a0zz0_1151352907.4377@excelforum-nospam.com...
>
> 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.

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" <muchacho.2a1vx4_1151394310.8099@excelforum-nospam.com> wrote in
message news:muchacho.2a1vx4_1151394310.8099@excelforum-nospam.com...
>
> 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
>

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" <muchacho.2a279w_1151409073.1673@excelforum-nospam.com> wrote in
message news:muchacho.2a279w_1151409073.1673@excelforum-nospam.com...
>
> 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?

Not without seeing it.

--

HTH

Bob Phillips

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

"muchacho" <muchacho.2a2l5c_1151427005.6546@excelforum-nospam.com> wrote in
message news:muchacho.2a2l5c_1151427005.6546@excelforum-nospam.com...
>
> 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.

Just bumping the post.