SumIf query ...

M

muchacho

Does this function ignore invisible cells (autofilter) ?

If not, what could be an alternative
 
M

Marcelo

use subtotal(9,range)

on the sunif to ignore hides rows

HTH
regards from Brazil
Marcelo

"muchacho" 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)
 
M

Marcelo

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:
 
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
 
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)
 
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
 
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.
 
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)
 
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)
 
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
 
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)
 
M

muchacho

Any idea why, if I copy and paste data into the spreadsheet the formula
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)
 
Top