Count If If Type of formula

**StuartBrookes** · 11-20-2009, 01:42 PM

Wondering if anyone can help me...

Problem 1:

I have Column B that can be any one of 6 values.
I have Column K that is the total value of a sale.

I want something that does

If "B3:B500=value1" Count K3:K500.

I've tried Count If, but it will only do the CountIf of 'value 1' in Column B.

I'm assuming it must be some sort of array or a CountIfIf formula, but thats not really my speciality...

Problem 2:

I've then got a 2nd formula, that is essentially adding in a 3rd column.
Column B can be any one of 6 values e.g. implementing
Column C can be any one of 10 values e.g. Consulting
Column K is the value of the sale (integer)

I want something that does the following "If Column B3:B500=Implementing AND Column C3:C500=Consulting, COUNT K3:K500"

Any ideas?

Many thanks in advance.

**teylyn** · 11-20-2009, 01:50 PM

Hi,

to add per each value in B, create a new column, say M, list your six individual values in M1 to M6, then use a formula like this in an adjacent column (or anywhere else on the sheet) and copy down:

=SUMIF($B$3:$B$500,M1)

The second issue can be resolved with

"If Column B3:B500=Implementing AND Column C3:C500=Consulting, COUNT K3:K500"
=SUMPRODUCT(--($B$3:$B$500="Implementing"),--($C$3:$C$500="Consulting"),$K$3:$K$500)

Overall, it might be better to use a pivot table to slice, dice, sum, count and group the information.

hth

Count If If Type of formula

LinkBack

Thread Tools

Rate This Thread

Display

Count If If Type of formula

Re: Count If If Type of formula

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions