adding numbers for multiple text lines

**nylarising** · 10-30-2007, 04:40 PM

i'm definitely new to functions, but am trying to give it a whirl. COUNTIF did not work, neither did SUMPRODUCT (but I could be wrong).

i need to find and extract all line items from column X (e.g., containing the same text in multiple spots), locate all of the numbers/dollars corresponding to each in column Y, and provide the total count for the specified line item (identified in column X) in column Z. pls. help. thks.

**Excelenator** · 10-30-2007, 04:47 PM

Can you post a sample of your data and explain what the desired result should be?

**nylarising** · 11-01-2007, 12:16 PM

Sample data (in 4 columns, separated by | ) below:

Project | Scale | Division | Cost in 000's
X | Large | PTS | 1.5
Y | Large | TP&S | 1.5
A | Medium | PAS | .25
B | Large | PAS | 1.5
C | Large | PAS | 1.5
D | Medium | TP&S | .25
E | Large | GSS | 1.5
F | Small | TP&S | .07
G | Small | PTS | .07
H | Large | TP&S | 1.5

I need to itemize the total cost of each project scale (small, medium, large) in each division (without using a pivot table-yikes!). So, for example, if PTS has six Large scaled projects, the total cost for that division is 9.

any ideas?

**Excelenator** · 11-01-2007, 12:24 PM

Originally Posted by nylarising

(without using a pivot table-yikes!)

Pivot tables are EXACTLY what you need here!!! Why so afraid?

Well what you could do is Sort your data by Division then by Scale and then do subtotals on the Scale column.

**nylarising** · 11-01-2007, 01:32 PM

that's the only way i know how to do it. BUT i've also got to extract three separate pie charts showing the grand total for each project scale (small, medium and large) in each division. and, unfortunately, after using pivot tables, the pie chart wizard only wants to create for all large scale projects, even though it's told to do it for all. any clues on this?

and, btw, thanks for your help.