Search code examples
google-apps-scriptgoogle-sheetsgoogle-sheets-formula

Create a function in Google Sheets with these conditions

I'm using two different sheets, one for the values I'm going to need and one for counting the values of the first Sheet based on a number of conditions.

[Sheet 1] 1

[Sheet 2]
2

I'm using this formula to Count the values:

=(COUNTIFS('Sheet 1'!A:A;"*m003*";'Sheet 1'!A:A;"*m001*";'Sheet 1'!A:A;"*P165*";'Sheet 1'!B:B;1))/(COUNTIFS('Sheet 1'!A:A;"*m001*";'Sheet 1'!A:A;"*P165*"))

With this context, I'd like know if it'd be possible to create a function, macro or something that would let me do this operation by inputting some cells with the information I'd need instead of having to write "m003", "m001", "p165" or another value every time I want to execute the formula mentioned with any variation.

Solution

  • Replace them with cells:

    =(COUNTIFS('Sheet 1'!A:A;"*"&A1&"*";'Sheet 1'!A:A;"*"&C1&"*";'Sheet 1'!A:A;"*"&D1&"*";'Sheet 1'!B:B;1))/(COUNTIFS('Sheet 1'!A:A;"*"&C1&"*";'Sheet 1'!A:A;"*"&D1&"*"))

    where,

    A1: m003
    C1: m001
    D1: P165