AverageIF or Avg(Collect) formulas

Deesa Dontamsetti

Hi All,

I have multiple criteria and thus used the AVERAGEIF statement, however, this is not working cause this formula only limits to one criteria, and I need to add multiple criteria. For example, we are able to do this with COUNTIFS statement, however, there is not AVERAGEIFS statement.

Since AVERAGEIF is not working for me because I need multiple criteria to be used, I tried AVG(COLLECT), however, I am getting unparseable.

Has anyone worked with AVG for multiple criteria? I need only one range for the average, however, need multiple filters in the formula. Further, one of the criteria includes "unchecked" numbers to be pulled for which I used "0". I even looked at IF(AVERAGEIF) statements in community, but looks like that's not solved with unchecked numbers as one of the criteria. Please let me know if anyone has any ideas. Thanks so much in advance!

David Tutwiler

You could create the average calculation by creating a SUMIFS and a COUNTIFS for the numerator and denominator respectively. Something like:

=SUMIFS(criteria that you want to look at to sum up your totals)/COUNTIFS(criteria to count how many rows met your criteria)

Please @mention me with any replies so I can be notified and answer any questions you might still have with this.

Deesa Dontamsetti

Hi @David Tutwiler

Thanks for your response. Appreciate it. I was trying to calculate countifs (for the denominator), however, smartsheet is including counts of blank values too...is there a way to avoid this?

Thanks for your help!

David Tutwiler

Have you tried adding NOT(ISBLANK(@cell)) as one of the conditions in the COUNTIF?

Deesa Dontamsetti

I am not able to select the column for ISBLANK. I want it to be NOT(ISBLANK(@column)). Since there are several blanks in the column which have to be avoided. But looks like for ISBLANK I can only use one value. Thanks! @David Tutwiler

David Tutwiler

Both the SUMIF and COUNTIF function go row-by-row checking each row against the criteria you define. If you add the formula in my last message where the ISBLANK is looking either @cell or whichever column contains a blank and @row then it will throw out any rows that have blanks.

Or are you wanting to run another calculation if there are any blanks at all in the column?

Deesa Dontamsetti

Yes, I tried using ISBLANK but it doesn't work for my calculation. I think the 2nd one to run a calculation if there are any blanks in the column...the column I am looking at right now does have couple of blanks though and those need to be excluded from the COUNTIF statement. Thanks! @David Tutwiler

David Tutwiler

Can you please describe what didn't work in putting the check for blanks in the COUNTIF statement? The only way I know how to check for a whole column would be to set up a helper column to check for blanks that is either checked or not. Although, the COUNTIF within the formula should work.

Deesa Dontamsetti

=COUNTIFS({Rev Rqsts Range 8}, [Protocol Type]1, {Rev Rqsts Range 1}, "0", {CPO Form Range 1}, NOT(ISBLANK({CPO Form Range 1}))).

I used the above. CPO Form Range 1 is the column that has some blank cells. The ones which are not blank have numbers. The other two Rev Rqsts Range 8 and Rev Rqsts Range 1 are used to filter the data.

Deesa Dontamsetti

If I exclude the blanks, then I have the correct number (denominator) to calculate the average...right now smartsheet is adding the blanks to the count number. Also sorry forgot to tag in the above comment I sent with the formula @David Tutwiler

David Tutwiler

No problem. Try this:

=COUNTIFS({Rev Rqsts Range 8}, [Protocol Type]1, {Rev Rqsts Range 1}, "0", {CPO Form Range 1}, NOT(ISBLANK(@cell)))

This formula now says "Count if the Rev Req Range 8 is equal to Protocol Type in Row 1 AND if Rev Req Range 1 is 0 AND the cell in CPO Form Range IS NOT BLANK"

Deesa Dontamsetti

It worked! Thanks so much for your help! @David Tutwiler

David Tutwiler

Awesome! Glad it's working.

ana_outset

Hi @David Tutwiler thank you so much for that formula it helped be a lot!

I've encountered another challenge and tried to fix it without success, hope the community can shed some light on the matter.

I need to use AVERAGE IF formula with two criteria, right now this formula is working okay:

=AVERAGEIF({[Range 1}, NOT(ISBLANK(@cell)), {Sum Range})

but now I need to include this argument:

{Range 2}, Segment@row

Is currently as follows but it is giving me an "INCORRECT" or "UNPARSEABLE" result

=AVERAGEIF({Range 1}, Segment@row, {[Intake Sheet] - Range 2}, NOT(ISBLANK(@cell)), {KPI Adherence})

ana_outset

SUMIFS

=SUMIFS({KPI Adherence}, {Segment - Intake Sheet}, Segment@row, {[Intake Sheet] - Project Health Scorecard Range 1}, NOT(ISBLANK(@cell)))

COUNT IFS

=COUNTIFS({Segment - Intake Sheet}, Segment@row, {[Intake Sheet] - Project Health Scorecard Range 1}, NOT(ISBLANK(@cell)))

How can I put it together and divide in just one formula? @David Tutwiler

David Tutwiler

I used a test sheet and named references that you mentioned in your second comment and this should work:

=SUMIFS({KPI Adherence}, {Segment - Intake Sheet}, Segment@row, {[Intake Sheet] - Project Health Scorecard Range 1}, NOT(ISBLANK(@cell))) / COUNTIFS({Segment - Intake Sheet}, Segment@row, {[Intake Sheet] - Project Health Scorecard Range 1}, NOT(ISBLANK(@cell)))