Results 1 to 15 of 17

20100525, 21:33 #1
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
I need to count number of occurences Col A has a "R" and col B has "VI"
I tried =SUM((A1:A5="R")*(B1:B5="VI")) but get 1, even though there are 3 occurences. If I click the fx symbol to the left of the formula bar the drop down gives a value of 3. It seems to only show the value on that row, not the total.
Len

20100526, 04:07 #2
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,322
 Thanks
 3
 Thanked 214 Times in 197 Posts
You actually need SUMPRODUCT:
=SUMPRODUCT((A1:A5="R")*(B1:B5="VI"))Regards,
Rory
Microsoft MVP  Excel

20100526, 13:03 #3
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Can you use a "count" or "countif" formula?
Len

20100526, 13:56 #4
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
Will this work for you?
=COUNTIF(A1:A5,"r")+COUNTIF(B1:B5,"vi")
Please note it is not case sensitive.

20100527, 17:50 #5
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Tried that but it does not count occurences where both arguments occur on same row, but rather the # of occurences where either exists. It just gives a sum of all occurences regardless of "matches". See sample. I want to see how many times an "R" and VI are on same row.

20100527, 18:53 #6
 Join Date
 Feb 2009
 Location
 Kings Park, Victoria, Australia
 Posts
 90
 Thanks
 15
 Thanked 5 Times in 5 Posts
Len, In column C add the following formula =IF(AND(A1="r",B1="vi"),1,0) copy it down as many rows as needed, then sum column C.
Regards,
MariaMaria
Simmo7
Victoria, Australia

20100527, 20:25 #7
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
Hi Len  Thanks for supplying the sample spreadsheet. That always helps to get responses.
There are many possibilities to get what you want. I have attached an example. Col C can be hidden if necessary or Col C formulas can be calculated elsewhere on your worksheet.
Like previous solutions, this only works when the "R" is in Col A and the "VI" is in Col B. Do you ever have "VI" in Col A and the "R" in col B?
Continue to let us know if something different is needed.
Tim

20100527, 20:45 #8
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Thanks Maria, but that would not be efficient with many entries if I had to do each line. I have only one variation in "R" column [it is present or absent], but 10 variations in the "VI" column and about 300 entries to calculate. I need to calculate individually how many [total/sum] of each of these variations have a "R" associated on same row.
Example of desired results in about 300 entries:
There are 10 "VI" with an "R"
There are 7 "DD" with an "R"
There are 2 "BS" with an "R"
There are 12 "4T" with an "R"
There are 0 "2T" with an "R"
There are 8"Mn" with an "R"
etc
Intuitively, this seems that this should be a "count" function, but strangely it does not appear to be.

20100527, 20:56 #9
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
thanks everyone. I do not think I was clear nor was my samle xls. Attached is a better sample. I need to know how many [ individually ] VI, DD, etc have an "R" associated with that entry.
VI=4 [with "R"]
DD=5
bs=1
4t=5
etc

20100527, 20:58 #10
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
forgot attachment

20100527, 21:20 #11
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
Hi Len  The examples keep getting clearer. Thanks for attaching that file.
Does this new file (attached) give answers closer to what is desired? This solution uses an Excel Pivot Table.
The data can be as many rows as needed and as many different variations in col A or Col B as needed.
The view can be all of Col A or just "R" (or any other variable) in col A or just "blanks" in col A.
The view can also be Just Col A with an "R" and "VI" (or any other variation) in col B
There is no VB/Macro coding using this Pivot Table. Pivot Table is a very useful Excel feature.
Tim

20100528, 04:50 #12
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,322
 Thanks
 3
 Thanked 214 Times in 197 Posts
Did you try the SUMPRODUCT at all?
Regards,
Rory
Microsoft MVP  Excel

20100528, 11:36 #13
 Join Date
 Dec 2009
 Location
 East Coast, USA
 Posts
 993
 Thanks
 8
 Thanked 43 Times in 43 Posts
Hello  The attached file shows 2 possible solutions. They are shown on separate tabs (Pivot Table and SUMPRODUCT), although they could be displayed with the original data.
Tim

20100528, 12:03 #14
 Join Date
 Dec 2009
 Posts
 212
 Thanks
 36
 Thanked 0 Times in 0 Posts
Rory,
Yes, it works and I thank you for your input and assistance, but I am trying to become more proficient with Excel and and my project seems such a basic function for Excel that I assumed more approaches exist . The "countif" or "sumif" functions seem so intuitive here that I assumed one or both would be a valid and "neat" approach. I have learned "countif" does not work with more than 1 argument, but "sumif" I am still puzzled.

20100528, 12:20 #15
 Join Date
 Dec 2000
 Location
 Burwash, East Sussex, United Kingdom
 Posts
 6,322
 Thanks
 3
 Thanked 214 Times in 197 Posts
Your original formula should work if you array enter it, but neither sumif nor countif accept multiple criteria. If you have 2007 or 2010 you can use COUNTIFS though.
Regards,
Rory
Microsoft MVP  Excel