# Thread: simple count/sum with 2 arguments

1. 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

2. You actually need SUMPRODUCT:

=SUMPRODUCT((A1:A5="R")*(B1:B5="VI"))

3. Can you use a "count" or "countif" formula?

Len

4. Will this work for you?

=COUNTIF(A1:A5,"r")+COUNTIF(B1:B5,"vi")

Please note it is not case sensitive.

5. 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.

6. 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,
Maria

7. 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

8. 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.

9. 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

10. forgot attachment

11. 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

12. Did you try the SUMPRODUCT at all?

13. 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

14. 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.

15. 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.

Page 1 of 2 12 Last

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•