hey my friends
pls open the file which is attached
in 2nd worksheet (2013_filtered) , u ll see my sales number only for 2013 but when i write the formula, u see result of COUNTIFS formula again for both 2012 and 2013 is 10 . but result should be 5 for only 2013 data.
so how i should write "COUNTIFS" formula only for seen data in the table. i dont wanna "COUNTIFS" formula test hidden data when i filtered.
Formula:
Please Login or Register to view this content.
Regards
Fotis.
-This is my Greek whisper to Europe.
--Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.
Advanced Excel Techniques: http://excelxor.com/
--KISS(Keep it simple Stupid)
--Bring them back.
---See about Acropolis of Athens.
--Visit Greece.
Maybe this...
=SUMPRODUCT(SUBTOTAL(3,OFFSET(B2,ROW(B2:B23)-ROW(B2),0)),--(B2:B23>15000))
Biff
Microsoft MVP Excel
Keep It Simple Stupid
Let's Go Pens. We Want The Cup.
=SUM(IF(SUBTOTAL(109,OFFSET(B1,ROW(A1:A22),0,1,1))>15000,1,0))
as an array formula (confirm with ctrl+shift+enter)
I would add a helper column in C
In C2 and filled down, put
=SUBTOTAL(2,B2)
Then use
=COUNTIFS(B:B,">15000",C:C,1)
fyssas, fotsis and finally i met u FOTIS....
hey from bodrum.
can u enter formula with attached file please once more?
i dont like helper column method honestly JONMO1Originally Posted by Jonmo1
Hi from Athens
You watch football and basketball as well!!Well done!
Edit: I love helper columns!!
Last edited by Fotis1991; 02-24-2014 at 10:43 AM. Reason: Edit
Why?
They're NOT the end of the world.
Excel gives you over 16 thousand columns, might as well use a few.
Quite often the use of a helper column makes the overall calculation more efficient.
ahahha super!!!Hi from Athens
You watch football and basketball as well!!
Edit: I love helper columns!!
nice to meet u FOTIS1991
and thanks to all guys: Tony Valko , ragulduy , Jonmo1 for all answers
take care my friends!!!
You are welcome and thanks for the feed back.
As that takes care of your original question, please select Thread Tools from the menu link above and mark this thread as SOLVED,as per Forum Rule #9. Thank you.
Also, as a new member of the forum, you may not be aware that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.
Now that I think about it, ragulduy's suggestion is the better method but I would write it like this:
=SUMPRODUCT(--(SUBTOTAL(9,OFFSET(B2,ROW(B2:B23)-ROW(B2),0))>15000))
ur point of view more functional, my point of view is totally pragmatism with simple view as much as much possible.
sorry buddy : )))
No need to be sorry.
It's just a matter of opinion anyway.
Glad you have resolution one way or another.
You're welcome. We appreciate the feedback!and thanks to all guys: Tony Valko , ragulduy , Jonmo1 for all answers
take care my friends!!!
good morning my friends,
can u help me pls about my attached file
i added one more Column which shows sales for which country
i wanna count the Sales which is over 15000 only to Greece
can u help pls?
Hello neighbor!!
Try this one.
Formula:Please Login or Register to view this content.
καλημέρα καλημέρα : ))
thanks a lot my friend : ))
i hope u re oke : )))
take care
only can u add the formula in my xlsx file please? my excel is turkish so its getting problem for to translate it to turkiish : ))
also it looks like there is error in 3,OFFSET part.
Last edited by aaaaa34; 03-03-2014 at 07:50 AM.
Ok. Here you are..!
bravo fotis : )))
u always add values to OUR FRIENDSHIP AND PEACE :pp
I wish to do this my friend. Very happy if i can do this!
Regards!
Be sure u do this MY BUDDY!!!
in june i ll support Hellas with u in brasil : )))
take care
see ya
ya i know
i really made u bored : )))
but last last last info i need my friend so sorry
How can i count total rows appear when i filter only for 2012 ?
u didnt see my #23 msg yesterday i think Fotis
It's true. I didn't see it!
Formula:Please Login or Register to view this content.
This is your formula.
In fact is the same formula with slight modifications.
It would be useful to study a little and to try to work according your needs.
No need to be sorry. It's not a big deal..!..i really made u bored : )))
but last last last info i need my friend so sorry
Have a nice evening!
Fotis,
thank u so much for ur answer once more.
but, u wrote a formula with adding year column filter
i mean,
if i used 3-4 filters at the same time, cant we write more general formula for counting how many rows seen in that moment in sheet?
can u look to my this attached file please?
and can u add possible solution formula inside the sheet and send me back new one please?
01112347.xlsx
Hello.
Probably you mean(?)
Formula:Please Login or Register to view this content.
yup exactly MY NEıGHBOR!!!
THANK U SO MUCH!!!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks