Search code examples
excelif-statementexcel-formulareference

Excel count if relative reference

I want to count number of occurrences for items listed in column A only IF the numbers in column B are greater than 0. In column E, I would like to have the results, that is # of occurrences for items listed in column A, counted only if corresponding number in Column B is greater than 0. I have over a thousand of items in column A. I have put the results I would like to see in column E.

Please select this link (or copy and paste to a web browser) to view this excel in a web browser in case the image I added does not display.

enter image description here

I tried different things but cannot make it work.

Solution

  • Try using COUNTIFS() function:

    enter image description here

    =COUNTIFS(A2:A9,C2:C5,B2:B9,">0")

    If it has to be dynamic in a way that it has to pull all the data into one single array then how about using one of the following:

    enter image description here

    =LET(
     _Data, A2:B9,
     _Fruits, TAKE(_Data,,1),
     _ListedCounts, TAKE(_Data,,-1),
     _Occurrences, COUNTIFS(_Fruits,_Fruits,_ListedCounts,">0"),
     UNIQUE(HSTACK(_Fruits,TEXT(_Occurrences,"[=1]0 \O\c\c\u\r\r\e\n\c\e;0 \O\c\c\u\r\r\e\n\c\e\s"))))

    Or,

    enter image description here

    =LET(
     _Data, A2:B9,
     _Fruits, TAKE(_Data,,1),
     _ListedCounts, TAKE(_Data,,-1),
     _Occurrences, COUNTIFS(_Fruits,_Fruits,_ListedCounts,">0"),
     UNIQUE(HSTACK(_Fruits,_Occurrences,IFS(_Occurrences=1,"Occurrence",TRUE,"Occurrences"))))

    Like I have already posted using COUNTIFS() function which is primary function here and does main job, lets explain a bit abt the rest of the functions:

    • Using LET() function it becomes easier to read and use variables for ranges/functions.
    • _Data refers to whole of data, usually in the forum we suggest to use Structured References instead of using the whole ranges, which slows the excel functionality. Tables makes easier to adjust the ranges automatically by rows/columns. However i have used only the ranges specified. No blank rows taken into account.
    • _Fruit refers to the fruits columns i.e. A2:A9 using TAKE() function to extract it.
    • _ListedCounts like wise above used TAKE() function to the specific column.
    • _Occurrences refers to the frequency of the number of times fruits appeared with a value greater than 0
    • Using UNIQUE() function to get unique values from the merged data combined using HSTACK() of the three arrays _Fruits, _Occurrences as well as the additional col with specific words as Occurrence or Occurrences to get the desired output.

    Note: In the second method I have used a custom formula in the TEXT() function which uses backslash which is an escape character. It serves a different purpose, we are escaping it meaning hence asking Excel to literally form text with that character.