Sumif in selected rows only

[drluke]

A

B

C

D

E

1

A526

Dec-15

North W

56.25

548.75

2

A526

Dec-16

South E

56.25

3

A526

Dec-17

Wales

100.00

4

A526

Mar-18

Scotl

26.25

5

A526

May-18

Ldn A

112.50

6

A526

Jul-18

Ldn B

103.75

7

A526

Aug-18

Wales

93.75

8

A897

Jan-18

North W

769.67

1,840.69

9

A897

Feb-18

South E

669.50

10

A897

Mar-18

Wales

58.40

11

A897

Apr-18

Scotl

28.00

12

A897

May-18

Ldn A

120.12

13

A897

Jun-18

Ldn B

195.00

I currently have a sum formula in E1, E8 etc., but I would rather have a formula that I can copy down all of the rows that will

(a) sum all the amounts in column D for all rows that have the same code in col, A

(b) the result in col D must only be on the first row where there is a change in col A

Any help would be much appreciated.

<colgroup><col width="64" style="width: 48pt;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="64" style="width: 48pt;"> <col width="65" style="width: 49pt; mso-width-source: userset; mso-width-alt: 2377;"> <col width="64" style="width: 48pt;"> <col width="74" style="width: 56pt; mso-width-source: userset; mso-width-alt: 2706;"> <col width="73" style="width: 55pt; mso-width-source: userset; mso-width-alt: 2669;"> <col width="64" style="width: 48pt;" span="6"> <tbody> </tbody>

 

[steve the fish]

Is the data sorted by column A? If so:

=IF(COUNTIF($A$1:A1,A1)=1,SUMIF(A:A,A1,D:D),"")

 

[drluke]

Yes it is! Thank you this works.
If I added 2 additional columns E & F, how could I change the formula to sum columns D,E & F with the result in col G?

 

[jtakw]

Hi,

Looks like OP started a new thread for this question here:

https://www.mrexcel.com/forum/excel-questions/1070699-sumifs-range-columns-sum.html#post5142592

 

[Aladin Akyurek]

Yes it is! Thank you this works.
If I added 2 additional columns E & F, how could I change the formula to sum columns D,E & F with the result in col G?

Control+shift+enter, not just enter:

=IF(COUNTIFS($A$1:A1,A1)=1,SUM(IF($A$1:$A$13=A1,$D$1:$F$13)),"")