# Counting and averaging with a mix of IF and OR

#### bluesky6688

##### New Member
Good evening~

I am trying to analyze some experimental data I recently collected. I need some complicated formula but I am not an advanced Excel user. Any help is greatly appreciated.

So, below is a simplified data set with 3 formula I am trying to create:

 1 A(ID) B C D 2 a IL L2 3 3 a PL L3 4 4 a IL L5 5 5 a IL L2 6 6 b IL L5 7 c PL L3 8 c IL L2 9 d PL L3 10 e IL L3 5

<tbody>
</tbody>

Calculation
Formula A (FA): to calculate the average all cells in D that satisfy: (B2:B10="IL")+(C2:C10="L2" or C2:C10="L3)+ISNUMBER(D2:D10).
Result: mean = 4.67 (3,6,5)

FB: Count all number of cells in D that used to calculate the average above.
N = 3 (3, 6, 5)

FC: Count all non blank cells in column D that satisfy these criteria: (B2:B10="IL") + (C2:C10="L2" or "L3") + (unique value in A2:A10).
n = 2 (a,e)

I made FA ={AVERAGE(IF(OR(C2:C10=A12,C2:C10=B12),IF(B2:B10=D21,IF(ISNUMBER(D2:D10),D2:D10))))}. The result is wrong 4.75, which is the average of (3,5,6,5), and is not what I expect (average of 3,6,5).

I will also need to calculate STDEV, I assume that will be similar with average.

Thanks again!

### Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### dermie_72

##### Well-known Member
here is what I would do on the first one. See if that helps with the rest. To be honest though, this sounds suspiciously like homework.

Formula A (FA): to calculate the average all cells in D that satisfy: (B2:B10="IL")+(C2:C10="L2" or C2:C10="L3)+ISNUMBER(D2:D10).
Result: mean = 4.67 (3,6,5)

=(sumifs(D2:D10,B2:B10,"IL",C2:C10,"L2")+sumifs(D2:D10,B2:B10,"IL",C2:C10,"L3"))/(countifs(B2:B10,"IL",C2:C10,"L2")+countifs(B2:B10,"IL",C2:C10,"L3"))

#### bluesky6688

##### New Member
here is what I would do on the first one. See if that helps with the rest. To be honest though, this sounds suspiciously like homework.

Formula A (FA): to calculate the average all cells in D that satisfy: (B2:B10="IL")+(C2:C10="L2" or C2:C10="L3)+ISNUMBER(D2:D10).
Result: mean = 4.67 (3,6,5)

=(sumifs(D2:D10,B2:B10,"IL",C2:C10,"L2")+sumifs(D2:D10,B2:B10,"IL",C2:C10,"L3"))/(countifs(B2:B10,"IL",C2:C10,"L2")+countifs(B2:B10,"IL",C2:C10,"L3"))

Dear Dermie_72,

Thanks for your help, but this is not a homework. These are my real experimental data.
I used to import my data to SPSS then do all the stats. Now I love to use the formula to make some summary lines in a new sheet so that I can visualize the trend as I am collecting data. Calculating the average, n, STDEV, and stand error is the most part of my statistic. I can finally check the significant levels in SPSS when I want.

Regarding your formula, the sum is correct but the counting is incorrect. It doesn't exclude blank cells, but again I appreciate for your help.

#### dermie_72

##### Well-known Member
to exclude the blank cells, I would add d2:d10,"<>""" into the countifs statement to see if that works.

##### Active Member

just to correct the above

=AVERAGE(IF((C2:C10=A12)+(C2:C10=B12),IF(B2:B10=D21,IF(ISNUMBER(D2:D10),D2:D10))))

the plus sign is for "or" ->(C2:C10=A12) OR (C2:C10=B12)
and for "and" use asterisk (C2:C10=A12)*(C2:C10=B12)

• bluesky6688

#### bluesky6688

##### New Member
just to correct the above

=AVERAGE(IF((C2:C10=A12)+(C2:C10=B12),IF(B2:B10=D21,IF(ISNUMBER(D2:D10),D2:D10))))

the plus sign is for "or" ->(C2:C10=A12) OR (C2:C10=B12)
and for "and" use asterisk (C2:C10=A12)*(C2:C10=B12)

This works for all that I need! I can just replace the "average" with "count" or "STDEV" to get other results.

Thanks for helping!

#### bluesky6688

##### New Member

Now I still need help for my last formula, FC. I want to count the # of unique subjects in A2:A10, only the ones that have numbers in D2:D10.

I know the the formula to count unique values is ={SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))}, the result is 5 (a,b,c,d,e). How to add a criteria to exclude the ones that don't have any values in D2:D10, so the result will be 2 for my example data (a,e)?

Thanks a lot!

##### Active Member
try something like this
array also
=SUM(IF(FREQUENCY(IF(D2:D10<>"",MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1),1))

edit: if you want to list them say in Column I4
array also
=INDEX(\$A\$2:\$A\$10,MATCH(0,IF(\$D\$2:\$D\$10<>0,COUNTIF(\$I\$3:I3,\$A\$2:\$A\$10)),0))

Last edited:

##### MrExcel MVP
Now I still need help for my last formula, FC. I want to count the # of unique subjects in A2:A10, only the ones that have numbers in D2:D10.

I know the the formula to count unique values is ={SUM(IF(FREQUENCY(IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""), IF(LEN(A2:A10)>0,MATCH(A2:A10,A2:A10,0),""))>0,1))}, the result is 5 (a,b,c,d,e). How to add a criteria to exclude the ones that don't have any values in D2:D10, so the result will be 2 for my example data (a,e)?

Thanks a lot!

1. Control+shift+enter...

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)),IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))),1))

Even better:

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1),1))

2. Control+shift+enter...

=SUM(IF(FREQUENCY(IF(A2:A10<>"",IF(D2:D10<>"",MATCH(A2:A10,A2:A10,0))),ROW(A2:A10)-ROW(A2)+1),1))

• bluesky6688

#### bluesky6688

##### New Member
1. Control+shift+enter...

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)),IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0))),1))

Even better:

=SUM(IF(FREQUENCY(IF(A2:A10<>"",MATCH(A2:A10,A2:A10,0)),ROW(A2:A10)-ROW(A2)+1),1))

2. Control+shift+enter...

=SUM(IF(FREQUENCY(IF(A2:A10<>"",IF(D2:D10<>"",MATCH(A2:A10,A2:A10,0))),ROW(A2:A10)-ROW(A2)+1),1))

All these work perfectly! Thank you and all the guys that helped me!

Replies
1
Views
386
Replies
5
Views
200
Replies
6
Views
106
Replies
6
Views
246
Replies
7
Views
165

### Forum statistics

1,130,112
Messages
5,640,173
Members
417,129
Latest member
geekzilla ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back