formula to return value of several rows + columns

**n1kk1** · 09-14-2010, 02:09 PM

Hi,

I hope someone can help me with what I am sure is a simple formula.

I need the ‘Summary’ in column ‘S’ to look in the ‘artist ref’ in column ‘D’ and count how many SM002’s there are at A2 size (column H). I would like the summary to return this for each of the artist references

To make sense of all this it is a summary of poster sizes per image – as the print costs are cheaper to print lots of an individual image I need to work out how many of each image per size, I know there will be a way in excel to do this but can’t figure out how!

**ChemistB** · 09-14-2010, 02:32 PM

First, change AA5 to Industrial Frame. Then in T6

=SUMPRODUCT(--(INDEX($G$6:$O$54,,MATCH(T$5,$G$2:$O$2,0))),--($D$6:$D$54=$S6))

Dragged across and down. This gives for SM002;
A0 0
A1 4
A2 6
White Box 0
Black Box 0
White Slim 0
Black Slim 1
Industrial Frame 5
Is that what you were looking for?

**Cheeky Charlie** · 09-14-2010, 02:33 PM

I think you want:
=SUMIF($D:$D,$S6,G:G)
in T6, rather than write two formulae, it would be a good idea to remove that spacer column at J.

You can copy the formula I've given you across for sizes, and down if you add more references in S.

The better way of doing this would be a pivot table, but I'd rather not describe it if you aren't interested in a bit of extra legwork (to me it's worth the power and flexibility of a pivot table)

hth

**n1kk1** · 09-15-2010, 03:44 AM

Hi, thank you both for these it is exactly what i am looking for. The only other part would be for Column S to look for the image ref in column D so it only returns 1 instance of each, do you know how I would do this?

**Cheeky Charlie** · 09-15-2010, 03:56 AM

A pivot table would be the best way (by a long way).

Creating a unique filter of column D would be an alternative (a cheap and lazy one).

**n1kk1** · 09-15-2010, 04:30 AM

Originally Posted by Cheeky Charlie

A pivot table would be the best way (by a long way).

Creating a unique filter of column D would be an alternative (a cheap and lazy one).

Thanks Charlie, I will look into pivot tables but can you help me with a quick fix for now?

**Cheeky Charlie** · 09-15-2010, 04:37 AM

No probs
You need to type something into D5 so Excel can identify a heading (I typed Artist Ref)
Select the data in column D
Alt, d, f, a
Copy to another location
Copy to S5 (you will need to clear the contents of S5 first)
Unique records only

Done

I admit I've done it a few times before, but it takes me less time to create an operational pivot table than to run through these steps and create the appropriate formulae (pivot tables don't need formulae).

**n1kk1** · 09-15-2010, 04:48 AM

Originally Posted by Cheeky Charlie

No probs
You need to type something into D5 so Excel can identify a heading (I typed Artist Ref)
Select the data in column D
Alt, d, f, a
Copy to another location
Copy to S5 (you will need to clear the contents of S5 first)
Unique records only

Done

I admit I've done it a few times before, but it takes me less time to create an operational pivot table than to run through these steps and create the appropriate formulae (pivot tables don't need formulae).

sorry Charlie it is not recognising the Alt, d, f, a when I select the data column D (it is just entering Alt, d, f, a into the first cell) - I do have a mac so the shortcut may be different?

**Cheeky Charlie** · 09-15-2010, 07:47 AM

You are looking for 'advanced filter' (something like data->filter->?advanced/custom? - I don't have a mac)

hth

(It would help if you put your Excel version into your profile - I wrote the keyboard shortcut because that works in Excel 2000-03-07&10 - but not 08)