M
muchacho
Does this function ignore invisible cells (autofilter) ?
If not, what could be an alternative
If not, what could be an alternative
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
G
Guest
use subtotal(9,range)
on the sunif to ignore hides rows
HTH
regards from Brazil
Marcelo
"muchacho" escreveu:
on the sunif to ignore hides rows
HTH
regards from Brazil
Marcelo
"muchacho" escreveu:
G
Guest
SORRY IT'S DOESN'T WORK
MARCELO
"Marcelo" escreveu:
MARCELO
"Marcelo" escreveu:
B
Bob Phillips
what makes you think it doesn't?
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
G
Guest
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:
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:
M
muchacho
Surely I need an IF statement though.
What I want is for it to add some numbers up but only if the cel
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 b
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 I
statement
What I want is for it to add some numbers up but only if the cel
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 b
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 I
statement
G
Guest
=subtotal(9,range)
HTH
regards
Marcelo
"muchacho" escreveu:
HTH
regards
Marcelo
"muchacho" escreveu:
B
Bob Phillips
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)
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)
M
muchacho
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 HO
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) i
position 5
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 HO
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) i
position 5
M
muchacho
Hi Bob,
Can you talk me through the function you just gave to me ... what's i
doing
Can you talk me through the function you just gave to me ... what's i
doing
M
muchacho
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.
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.
B
Bob Phillips
=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)
!$G$1),,1))*
(Sheet2!$E$1:$E$200=1))
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
M
muchacho
I tried it but just can't get it to work.
If anybody would be happy with downloading the file and taking a loo
at it ...
http://www.realearners.biz/stats.xls
I've only got a few words in, as the original is over 6mb
If anybody would be happy with downloading the file and taking a loo
at it ...
http://www.realearners.biz/stats.xls
I've only got a few words in, as the original is over 6mb
B
Bob Phillips
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)
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)
M
muchacho
OK, cheers Bob, I'll wait for further notice
B
Bob Phillips
I couldn't use my favourite temporary file server, so I loaded it here
http://xldynamic.com/example code/NG - muchacho - Sumif query - stats.xls
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
http://xldynamic.com/example code/NG - muchacho - Sumif query - stats.xls
--
HTH
Bob Phillips
(replace somewhere in email address with gmail if mailing direct)
M
muchacho
Thanks for the help.
So what would I need to change if I have like 40,000 rows of data? (no
just the 3 when you looked at it).
I tried changing the $G$1:$G$20 to $G$1:$G$40000 and it displayed th
#N/A sign
So what would I need to change if I have like 40,000 rows of data? (no
just the 3 when you looked at it).
I tried changing the $G$1:$G$20 to $G$1:$G$40000 and it displayed th
#N/A sign
B
Bob Phillips
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)
change the other commensurately.
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
M
muchacho
Any idea why, if I copy and paste data into the spreadsheet the formula
doesn't work at just brings up zeros?
doesn't work at just brings up zeros?
B
Bob Phillips
Not without seeing it.
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
--
HTH
Bob Phillips
(replace xxxx in the email address with gmail if mailing direct)
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.