SUMIF???

[jer]

Dear all
In the following table I would like to sum column c based
on 2 conditions
A B C D
12-Sep-02 GRADE 1 1.15 0
22-Aug-03 GRADE 2 50.15 1
22-Aug-01 GRADE 2 25.45 3
26-Aug-02 GRADE 2 373.33 1
03-Sep-01 ANOTHER ONE 562.11 3
03-Sep-01 ANOTHER 2 8,048.31 1
04-Sep-01 ONE ANOTHER 10,592.42 2
09-Sep-01 ONE GRADE 2,479.28 1
09-Sep-02 2 GRADE 862.04 1

1 If any part of the values in column b is similiar
2 if the value in column d is equal

so that the resulting table will look something like
GRADE 1.15 0
GRADE 3,764.80 1
GRADE 25.45 3
ANOTHER 8,048.31 1
ANOTHER 10,592.42 2
ANOTHER 562.11 3

I am guessing it is a sumif however I am not sure how to
reference part of cell's in the formula. Or, some kind
of loop (I am relatively new to code) but again
I am not sure how to reference part of the cell's value.

Any help would be greatly appreciated
thanks
jer

 

[jer]

Thanks for your suggestion Bernard. However to expand a
little,
1 when I insert the sumif function it reads =SUMIF
(B1:B9,"Grade 1",D19), but what I want is just the
word "grade" or "account" in the first part of the
formula. The problem is because there is no consistency
in the format of values in B:B, e.g., values are "Grade
1", "grade 2", "one grade",etc. I am trying to avoid
doing multiple sumif's.
The real question I think is how to reference a of a
cell - just the word "grade"
2 Column d hold codes based on the date so that if the
code in d2 = 1 and d4 = 1, and any part of the value in
B1 or B4 is "grade" my result should be the sum of d2 and
d4, if B1 = grade 1 and b4 = another one then the result
should be the value in d1 alone
I hope this makes it a little clearer
thanks again
jer

 

[Alan Beban]

The real question I think is how to reference a of a
cell - just the word "grade"
2 Column d hold codes based on . . .

If you aren't going to provide an example, perhaps you should reread
what you're posting, slow down, and think about it. What was the above
supposed to mean?

Alan Beban

 

[Debra Dalgleish]

You could add a column to the table, in which you search for Grade or
Account. For example, in E2:
=IF(ISNUMBER(SEARCH("Grade",B2)),"Grade","Account")

Then, create a pivot table from the data. Add Columns B and D to the Row
area, and column C in the Data area.

There are some instructions and links for pivot tables here:
http://www.geocities.com/jonpeltier/Excel/Pivots/pivotstart.htm