Adding to this formula

Angliojoe

Board Regular
Joined
Oct 28, 2011
Messages
61
Hello All,

I have the following formula

=IF(COUNTIF($A$1:A2,A2)=1,COUNTIF(A:A,A2),"")

What it does is count the duplicates in the A column and returns a result of how many duplicates there are. I'm using this as I have a spreadsheet full of basket ID'd from on-line purchases and need to know volume per basket which is shown by the amount of times a basket ID is duplicated (once per product).

In column E it states whether a basket is a "club" member or "not".

I want to add to the above formula so it only checks baskets that are "club" and not baskets that aren't.

Is there anyway anyone could show me what the addition is? I tried a AND function but it wouldn't work. I'm relatively new to excel so your help is greatly appreciated.

Thanks,

Joe.
 

Excel Facts

Easy bullets in Excel
If you have a numeric keypad, press Alt+7 on numeric keypad to type a bullet in Excel.
Hope this helps

sumpic.jpg
 
Upvote 0
Rightly or wrongly, I think you are asking the following.....

In it's simplest form.... In column A you have basket ID's some of which will be duplicated because each row represents an instance of a product sold and there will be often more than one product per basket. There are two types of basket 'club' and not and you want the number of items per club basket.

Assuming that IS what you want then I can see no way that you can use a single formula. If you are not wishing to use vba then you can get a result as follows by introducing an extra column to hold an intermediate or 'helper' formula.
The helper formula can be in any column you have spare. It can be out of the way somewhere or can be hidden if you wish to avoid confusion.

You adjust the rows and columns to suit your data.

Here:
All starting with row 1
Basket ID in column A
Some unique identifier in column E say "CLUB" to indicate a club ID
Your volume count to appear in column B against the first instance of a qualifying ID
Helper column G joins the ID and the CLUB IDENTIFIER

The formula in B then counts the the duplicates in G and puts the value in the row of the first instance of the ID

Formula in B1 and copy down as far as you need.

IF($G1="","",IF(COUNTIF($G$1:$G1,$G1)=1,COUNTIF($G$G,$G1),""))

Formula in G1 and copy down as far as you need.

=IF($E1="CLUB",$A1&$E1,"")

I hope that is of help?
 
Upvote 0

Forum statistics

Threads
1,223,192
Messages
6,170,644
Members
452,344
Latest member
LarryRSch

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top