Counting Formula

[SpecialBrew]

Hi everyone. I would be extremely grateful if someone could help me with a counting formula?

In A1:A100 I have a list of locations (London, Sheffield, Leeds .... etc) - (non-unique)
In B1:B100 I have a list of colours + numbers (Red 1, Blue 15, Green 22 .... etc) - (unique)

In C1:C20 I have a unique list of the 20 locations.
In D1:D20 I have a column that looks up the location in C1:C20 and counts how many rows contain that location in column A and the word “blue” in column B.

Examples:
D1 contains how many rows contain the location (looked up in C1) in column A AND the word “Blue” in column B.
D2 contains how many rows contain the location (looked up in C2) in column A AND the word “Blue” in column B.

Columns E, F, G .... etc would count the totals for the other colours.

I have tried using COUNTIF, COUNT, SUMPRODUCT, Array Formulas (I still can’t get my head around array formulas!), but I just can’t seem to find anything that works.

(I’m using “*blue*” as the search for the colour to exclude the numbers in the cell.)

I THINK I have explained it OK.

Many thanks in anticipation of anyone’s help.

[Ron Coderre]

You indicate that you have Excel 2007, so:

With a sheet set as you described...
try this:

Please Login or Register  to view this content.

EDITED TO INCLUDE THIS EXCEL 2003 FORMULA:

Please Login or Register  to view this content.

Does that help?

[SpecialBrew]

Hi Ron,

Thank you so much for your quick reply. That does in fact work perfectly for me in 2007. Unfortunately (and I do apologize; I should have stated) it's for a 2003 sheet used at work.

Thank you.

Darrell.

[SpecialBrew]

Sorry Ron,

JUST noticed the second formula. I'll try it and let you know.

Thank you again in the meantime.

Darrell.

[daddylonglegs]

If you list the locations in C2:C21 and the colours in D1:Z1 or similar then you can use this version of Ron's suggested formula in D2 copied across and down:

=SUMPRODUCT(($A$1:$A$100=$C2)*(LEFT($B$1:$B$100,LEN(D$1))=D$1))

[SpecialBrew]

Hi Ron,

Thank you so much, that second 2003 solution works absolutely perfectly.

(Hopefully my headache might subside now!)

Sincere thanks for your very kind help.

Darrell.

[SpecialBrew]

Hi 'Daddylonglegs'

Thank you too. I shall do that very thing.

Thank you (both) again.

Darrell.