Welcome Guest.
You haven't logged in yet. If you don't have an account you can register now.

## trig42

Banana?
4537 posts

Uber Geek

Subscriber

Topic # 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

Banana?
4537 posts

Uber Geek

Subscriber

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

6770 posts

Uber Geek

Trusted

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.

Geekzone Live »

Try automatic live updates from Geekzone directly in your browser, without refreshing the page, with Geekzone Live now.

Geekzone Live »

Our community of supporters help make Geekzone possible. Click the button below to join them.