Excel formula question

Forums › Microsoft Windows › Excel formula question

trig42

5889 posts

Uber Geek
+1 received by user: 2094

ID Verified

#100167 4-Apr-2012 14:57

Hi.

I am trying to work out a sum of a range of cells, but only if another cell in the row that contains the number meets a certain criteria.
eg. (forgive formatting)

A B C ...

1 100 x
2 110 y
3 105 z
4 105 x
5 120 y
6 110 x

I want an average of everything with 'x' in column b.

I know how to count the number of x's in column b using COUNTIF, what I want is a SUM of all items in Column A that have an 'x' in column B.

Gurus...

trig42

5889 posts

Uber Geek
+1 received by user: 2094

ID Verified

#605113 4-Apr-2012 15:10

Got it.

sumif(B1:B6,"x",A1:A6) gives me the total from column A of everything with an x in coloumn B, then I just divide it by the COUNTIF(B1:B6,"x")

Works a treat.

Jaxson

8172 posts

Uber Geek
+1 received by user: 1332

Trusted

#605138 4-Apr-2012 15:46

Hey good stuff man.

I think there's scope for a geekzone wiki on certain topics, whereby if enough people click like etc that bit of info is transferred to the wiki, or something. This is a really good excel post, and it would be cool to collect it with other previous and future excel posts, rather than be buried in the threads etc.