Counting Instances

[mattyn]

Worksheet 1 has Column A with a number of instances of data, some repeated.

Column B has Y or N depending on whether the instance in Column A is Additional (Y) of not (N).

On a separate worksheet, I need to consolidate the data.. by counting how many of each instance there are, that are not additional (N). I am not interested in consolidating those that are additional.

I can see it needs some sort of If statement, but cannot work this out.

Also, I have a sample worksheet with an equation in that has curly brackets ({}) at each end. What do these mean in Excel? I see it means array but I don't understand what array is!

Thank you again in advance

Matt

 

[vllgv]

Assuming that there is some piece of data in say A1 of the separate worksheet that you want to count the instance of on the first worksheet where Additional = N.

In B1 on separate sheet write the formula:

=COUNTIFS(First_worksheet!A1:A7,A1,First_worksheet!B1:B7,"N")

The curly brackets mean that the formula is an array formula that needs to be entered by pressing Control + Shift + Enter. More info here:
https://support.office.com/en-za/ar...formulas-7d94a64e-3ff3-4686-9372-ecfd5caa57c7

 

[mattyn]

Thank you! That was quick! I will try it soon!

 

[mattyn]

Top result. Many many thanks.

Works perfectly.

Matt

 

[mattyn]

When I say it works perfectly, it does on my home PC -Excel 2010. At work, where I need it, Excel 2003, it doesn't!

Do IFS statements not work with Excel 2003?

 

[PichaiTC]

No, CountIFS does not exist before Excel 2007.
So you can change formula to be
={COUNT(IF(AND(First_worksheet!A1:A7=C1,First_worksheet!B1:B7="N"),1,0))}
Note: Type the formula without curly brackets ({}) then press Ctrl + Shift + Enter

 

[mattyn]

No, CountIFS does not exist before Excel 2007.
So you can change formula to be
={COUNT(IF(AND(First_worksheet!A1:A7=C1,First_worksheet!B1:B7="N"),1,0))}
Note: Type the formula without curly brackets ({}) then press Ctrl + Shift + Enter

Having difficulty with this - could not get it to count past 1, and I could not see why. However I did carry out a bit of "intellectual theft" from another firm's S/S who had this:

=SUM(IF(FirstWorksheet'!$B$8:$B$62='Secondworksheet'!$B23,IF('FirstWorksheet'!$G$8:$G$62="N",1,0))) all in the curly brackets!

And all is right with the world!

Many thanks again for your help. It is much appreciated.