simple count/sum with 2 arguments

Topic

New Reply

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

Reply | Quote

Replies

You actually need SUMPRODUCT:

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

Reply | Quote

Can you use a “count” or “countif” formula?

Len

Reply | Quote

Will this work for you?

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

Please note it is not case sensitive.

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

forgot attachment

Reply | Quote

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

Reply | Quote

Did you try the SUMPRODUCT at all?

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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.

Reply | Quote

Tim,
Thanks. The sumproduct Rory lead me to but I do not know how you got the “LenA” & “LenB” references to work, instead of the standard cell range[A1:A10] and referencing a different worksheet [sheet1!].
=SUMPRODUCT((LenA=”R”)*(LenB=B2))
How are you directing LenA [or LenB] to the data on “Sheet1”? Where is the LenA=Sheet1!A2:A30 ?

Also, I see the pivot table, but I am Pivot Table ignorant have no idea how you created it.

Len

Reply | Quote

Hi Len – I created a Range Name for your data in Col A – Rows 1-31 and called it LenA.
I created another Range Name for your data in Col B – Rows 1-31and called it LenB.

If you want to add more info to your original worksheet, Insert new rows anywhere between row 2 and row 30. The Range Names and formulas should update automatically.

If you need additional variables on the SUMPRODUCT tab, just type them one after another and copy the formula down.

Hope this helps.

Tim

Reply | Quote