Count a single range using additional criteria from another range

[FatFoot]

Hi all
I'm new here
What I'm trying to do is count the number of entries in a range of cells with a certain criteria - that part's easy (=countif(range,"code"), but I also want to add a criteria affecting a separate range which is loosely connected to this range (in attached workbook: if range AH16:AK35 = "g", and the colour shown in AO16:35 = 2nd criteria in the pricing sheet.)
I can count the number of the codes, but I need to count the number of codes using a certain colour which is displayed in a different range.

I know it's a very messy workbook but it's inherited & I'm trying to make it better .. *sigh*
Many thanks.
FatFoot

[XOR LX]

So are these criteria to add to an existing formula? Or to form a new formula on its own?

If it's the former, where are these formulae which you wish to amend (forgive me, but it's quite a mess in there!)? If it's the latter, where are these new formulae to be placed?

Regards

[FatFoot]

Thanks for the quick response
Basically, I'm trying to cost a door if it's made of gyprock (g) and is a certain colour. These values are shown in AH16:AK35 (type) and AO16:35 (colour). The doors in row 16 (AA) = the doors in 73 (B).
In the attached file, I want to put a value into the pricing sheet that tells me how many gyprock boards of a certain colour are to be used. (The prices change according to colour.)
At the moment, I can only tell how many of a certain size are used. { =COUNTIF('JOB CUTTING LIST'!$C$73:$F$97,PRICING!H$5) }
but I need the colour as well.
And yes, I know it's a mess, but you should have seen it a couple of days ago.

[XOR LX]

Hi,

So are these the formulas in cells H54:H65 of the PRICING tab? These are what you wish to be amended to include this new 'colour' criteria? And did you mean: COUNTIF('JOB CUTTING LIST'!$C$73:$F$97,PRICING!H$51) (this is what those formulas currently reference)?

And, if so, will these new formulas be referencing the relevant colours found in cells A54:A65?

Regards

[FatFoot]

That's it exactly ... thanks.

[XOR LX]

Ok,

I'm a little confused with these two ranges as they appear to be different sizes. The Type/Colour range (AK20:AO35) covers 20 rows whereas the GYPROCK PANEL PRICING table covers 25 (there are 5 extra rows for "J"). Actually, if you look at the bottom (extra) five rows in this table (P, Q, R, S and T), the formulas are looking at e.g. AC36-40, which are currently not part of your other table.

The upshot is that it's difficult to tell Excel to do come sort of criteria comparison/count if the ranges in question are not of the same size (number of rows in this case).

Can these two tables not be combined? Or at least made to be the same number of rows?

Regards

[FatFoot]

That's exactly what I ended up doing.
Thanks for your help; I've solved my problem, thanks to having someone else point out the bleeding obvious to me.