Counting formula
Hi Everyone,
Pls I need your help to looking for the formula
pls see the attached file, there are some outlets of different area such as North & South
and there ara some outlets with Outlet code. The outlet code is unique, but there are duplicates for different brands (Apple, Orange, Banana...etc)
So I want write a formula to looking for as Example area wise how many outlets have sold Banana or Apple ?
I have put the answers near the answering block.
Pls help me
Thanks
Book1.xlsx
Neranjan
if you want "apple or banana" this will work for you... countifs($C$4:$C$23,E28,$F$4:$F$23,"apple") and change "apple" to "banana" if you want that. If you want both then countifs($C$4:$C$23,E28,$F$4:$F$23,"apple")+countifs($C$4:$C$23,E28,$F$4:$F$23,"banana")
Make contributors happy, click on the "* Add Reputation" as a way to say thank you.
Sam Capricci
Hi Sambo kid, Thanks your reply, But I want to without duplicates (Outlet codes)
If have one outlet as one for "banana" and one for "Apple", I want that outlet count as 1 record.
Have you any formula for it ??
Thanks
ok, if I understand it correctly, are you opposed to adding a helper column? If you are ok with that, I'd put this formula in H4 and copy down...=COUNTIF($D$4:D4,D4) That will return a 1 for outlets that only appear once and a 2 (or higher) for any that appear more than once.
then use this for the count... =COUNTIFS($C$4:$C$23,E28,$F$4:$F$23,"apple",$H$4:$H$23,1)
Hi Sambo kid, Thanks for your reply, but the answer that I want has not captured. I want "How many outlets have sold Banana & Apple in South area ?" 3 outlets have sold more than 1 fruit
Thanks
ok, then use the helper column and use this countif formula... =COUNTIFS($C$4:$C$23,E29,$F$4:$F$23,"apple",$H$4:$H$23,1)+COUNTIFS($C$4:$C$23,E29,$F$4:$F$23,"banana",$H$4:$H$23,1)
Oh, Sambo Kid Thank you very much.Now its working
But Its grate if you can you try write this formula without helper colum ?
Now I am going to off (From the office). I will login on tomorrow morning.
Thanks you again
F28Try below array formula (Confirm with Shift+Ctrl+Enter)
Please Login or Register to view this content.
Samba
Say thanks to those who have helped you by clicking Add Reputation star.
or
Try below array formula (Confirm with Shift+Ctrl+Enter)Please Login or Register to view this content.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks