Sumif and left

Question

Sumif and left

Anonymous

I want to sum the contents in column D using =SUMIF(A5:A16,"=" & LEFT(A5,2),D5:D16) but i am geting a zero

A B C D

1 06/28/2012 22:00 0 13.12 24.76

2 06/28/2012 23:00 1 13.12 24.7

3 06/29/2012 00:00 2 13.11 24.65

4 06/29/2012 01:00 3 13.12 24.63

5 06/29/2012 02:00 4 13.11 24.6

6 06/29/2012 03:00 5 13.12 24.59

7 06/29/2012 04:00 6 13.11 24.58

8 06/29/2012 05:00 7 13.12 24.56

9 06/29/2012 06:00 8 13.12 24.55

I tried =SUMIF(A5:A17,"LEFT(A5,2)=06",D5:D17) but I still get a Zero

Can anyone help

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

Answer accepted by question author

3 additional answers

Answer 1

Anonymous

See my snap shot below with my dummy data, I would suggest use this formula at E2 and copy it down for other months.

=SUMIFS($B:$B,$A:$A,">"&EOMONTH(D2,-1),$A:$A,"<"&EOMONTH(D2,0))

Answer 2

Hello Lebohang

I am V. Arya, Independent Advisor, to work with you on this issue. You will need to use SUMPRODUCT. Use below formula

=SUMPRODUCT((LEFT(A5:A16,2)=LEFT(A5,2))*(D5:D16))

But this may not serve your purpose. Are you trying to sum on the basis of MONTH, then your formula would be

=SUMPRODUCT((MONTH(A5:A16)=MONTH(A5))*(D5:D16))

Please check your ranges.

Answer 3

See my snap shot below with my dummy data, I would suggest use this formula at E2 and copy it down for other months.

=SUMIFS($B:$B,$A:$A,">"&EOMONTH(D2,-1),$A:$A,"<"&EOMONTH(D2,0))

Thank you very much but please check below i am not getting the same results. i get different answers when using a formula and when manually calculating. what could be the problem

A B C D F

Date Values Date "Calculated Manual

by Fomula Calculation

01 October 2012 941.3027 Oct-12 228638.2347 238026.9075

01 October 2012 806.5257 Nov-12 171264.5059 180090.8843

01 October 2012 934.59 Dec-12 215942.5409 227225.9778

01 October 2012 906.5448 Jan-13 0

01 October 2012 862.3301 Feb-13 0

01 October 2012 784.2692 Mar-13 0

01 October 2012 489.6607 Apr-13 147368.1934

01 October 2012 0 May-13 314301.6112

01 October 2012 0 Jun-13 109304.659

01 October 2012 0 Jul-13 37768.67592

01 October 2012 0 Aug-13 16835.98742

01 October 2012 0 Sep-13 18136.29546

01 October 2012 0 Oct-13 17272.80322

01 October 2012 0 Nov-13 187112.6369

01 October 2012 0 Dec-13 203233.5689

01 October 2012 0 Jan-14 200638.8107

01 October 2012 0 Feb-14 18434.80236

01 October 2012 0 Mar-14 0

01 October 2012 0 Apr-14 0

01 October 2012 0 May-14 0

01 October 2012 230.8004 Jun-14 0

01 October 2012 667.0197 Jul-14 0

01 October 2012 796.2745 Aug-14 0

01 October 2012 865.0438 Sep-14 0

02 October 2012 885.5233 Oct-14 107830.9437

02 October 2012 701.4517 Nov-14 227941.6318

02 October 2012 685.7833 Dec-14 237934.1872

02 October 2012 529.6829 Jan-15 0

02 October 2012 630.0415 Feb-15 0

02 October 2012 326.9027 Mar-15 0

02 October 2012 0 Apr-15 48160.45316

02 October 2012 0 May-15 83732.96554

02 October 2012 0 Jun-15 103618.4212

02 October 2012 0 Jul-15 108000.8358

02 October 2012 0 Aug-15 149244.7091

02 October 2012 0 Sep-15 129145.6784

02 October 2012 0 Oct-15 49337.66758

02 October 2012 0 Nov-15 0

02 October 2012 0 Dec-15 0

02 October 2012 0

02 October 2012 188.0967

02 October 2012 568.9913

02 October 2012 705.6001

02 October 2012 429.0386

03 October 2012 60.5442

03 October 2012 644.1022

03 October 2012 732.2418

03 October 2012 680.1697

03 October 2012 641.7561

03 October 2012 400.6101

03 October 2012 0

03 October 2012 330.6418

03 October 2012 849.0422

03 October 2012 955.7763

03 October 2012 1001.461

04 October 2012 1018.273

04 October 2012 1002.861

04 October 2012 961.2086

04 October 2012 845.4271

04 October 2012 720.931

04 October 2012 433.9061

04 October 2012 0

04 October 2012 186.4581

04 October 2012 581.7284

04 October 2012 719.5772

04 October 2012 787.7872

05 October 2012 816.0111

05 October 2012 811.34

05 October 2012 700.3918

05 October 2012 126.6929

05 October 2012 263.2381

05 October 2012 362.258

05 October 2012 0

05 October 2012 210.8379

05 October 2012 599.3982

05 October 2012 704.1448

05 October 2012 486.2509

06 October 2012 127.2564

06 October 2012 18.22365

06 October 2012 0

06 October 2012 13.59012

06 October 2012 0

06 October 2012 0.9210184

06 October 2012 0

06 October 2012 44.70069

06 October 2012 18.55902

06 October 2012 14.85616

06 October 2012 554.7602

07 October 2012 1011.268

07 October 2012 1014.482

07 October 2012 993.2394

07 October 2012 952.7791

07 October 2012 873.3678

07 October 2012 648.3373

07 October 2012 0.1552761

07 October 2012 0

07 October 2012 340.284

07 October 2012 838.9998

07 October 2012 942.1001

07 October 2012 988.4423

08 October 2012 1005.816

08 October 2012 1010.358

08 October 2012 994.91

08 October 2012 954.0303

08 October 2012 871.6729

08 October 2012 658.1475

08 October 2012 0.8123541

08 October 2012 0

08 October 2012 359.1099

08 October 2012 869.4031

08 October 2012 971.2352

08 October 2012 1015.262

09 October 2012 1031.566

09 October 2012 1036.514

09 October 2012 1022.125

09 October 2012 982.9592

09 October 2012 905.457

09 October 2012 696.3656

09 October 2012 2.434475

09 October 2012 0

09 October 2012 360.6624

09 October 2012 873.0786

09 October 2012 972.0862

09 October 2012 1018.373

10 October 2012 1041.337

10 October 2012 1047.461

10 October 2012 1034.527

10 October 2012 998.5284

10 October 2012 913.5982

10 October 2012 726.2898

10 October 2012 5.26866

10 October 2012 0

10 October 2012 259.3275

10 October 2012 713.4379

10 October 2012 837.2723

10 October 2012 896.1156

11 October 2012 908.2268

11 October 2012 898.3696

11 October 2012 860.894

11 October 2012 822.0862

11 October 2012 764.8468

11 October 2012 538.2988

11 October 2012 5.426734

11 October 2012 0

11 October 2012 53.90845

11 October 2012 680.3172

11 October 2012 829.2175

11 October 2012 883.4213

12 October 2012 641.8687

12 October 2012 99.40994

12 October 2012 51.26167

12 October 2012 450.3292

12 October 2012 367.8543

12 October 2012 292.2694

12 October 2012 0

12 October 2012 63.07327

12 October 2012 50.35495

12 October 2012 61.5514

12 October 2012 52.82841

13 October 2012 96.66351

13 October 2012 159.3883

13 October 2012 4.382738

13 October 2012 6.200614

13 October 2012 67.28034

13 October 2012 63.72469

13 October 2012 15.19505

13 October 2012 0

13 October 2012 68.95811

13 October 2012 139.2165

13 October 2012 482.839

13 October 2012 885.4027

14 October 2012 1002.797

14 October 2012 1002.118

14 October 2012 992.1605

14 October 2012 957.9714

14 October 2012 879.8851

14 October 2012 625.3301

14 October 2012 19.61038

14 October 2012 0

14 October 2012 353.3298

14 October 2012 846.4182

14 October 2012 941.2415

14 October 2012 984.2415

15 October 2012 999.1967

15 October 2012 994.3822

15 October 2012 976.8657

15 October 2012 945.8265

15 October 2012 873.4679

15 October 2012 682.9152

15 October 2012 23.75504

15 October 2012 0

15 October 2012 356.3461

15 October 2012 848.5342

15 October 2012 950.2148

15 October 2012 991.9684

16 October 2012 1009.14

16 October 2012 1008.114

16 October 2012 989.4611

16 October 2012 954.4825

16 October 2012 875.553

16 October 2012 681.3038

16 October 2012 26.43357

16 October 2012 0

16 October 2012 360.9006

16 October 2012 853.8951

16 October 2012 951.3835

16 October 2012 994.2068

17 October 2012 1014.831

17 October 2012 1013.647

17 October 2012 1001.255

17 October 2012 965.6851

17 October 2012 885.0187

17 October 2012 681.11

17 October 2012 29.1775

17 October 2012 0

17 October 2012 357.1978

17 October 2012 832.2302

17 October 2012 885.921

17 October 2012 987.3814

18 October 2012 1016.177

18 October 2012 1018.733

18 October 2012 997.6109

18 October 2012 954.3995

18 October 2012 832.1241

18 October 2012 599.7846

18 October 2012 26.89177

18 October 2012 0

18 October 2012 328.6927

18 October 2012 801.9943

18 October 2012 902.9356

18 October 2012 954.871

19 October 2012 983.0631

19 October 2012 998.5376

19 October 2012 988.0226

19 October 2012 932.3303

19 October 2012 853.4968

19 October 2012 699.0876

19 October 2012 33.86275

19 October 2012 0

19 October 2012 309.1273

19 October 2012 744.1967

19 October 2012 866.5694

19 October 2012 923.5385

20 October 2012 945.9855

20 October 2012 941.499

20 October 2012 936.5069

20 October 2012 887.504

20 October 2012 584.8713

20 October 2012 536.1899

20 October 2012 12.76224

20 October 2012 0

20 October 2012 307.5695

20 October 2012 754.2571

20 October 2012 873.306

20 October 2012 970.8278

21 October 2012 988.7409

21 October 2012 988.7418

21 October 2012 979.8614

21 October 2012 962.7133

21 October 2012 880.945

21 October 2012 676.7421

21 October 2012 42.50515

21 October 2012 0

21 October 2012 417.0874

21 October 2012 878.6242

21 October 2012 973.9908

21 October 2012 1016.243

22 October 2012 1032.939

22 October 2012 1032.783

22 October 2012 1014.399

22 October 2012 980.1238

22 October 2012 924.9923

22 October 2012 738.0861

22 October 2012 53.05658

22 October 2012 0

22 October 2012 408.9126

22 October 2012 854.6783

22 October 2012 951.9645

22 October 2012 992.428

23 October 2012 1012.078

23 October 2012 1009.471

23 October 2012 995.9894

23 October 2012 962.4343

23 October 2012 890.1387

23 October 2012 693.8236

23 October 2012 55.40627

23 October 2012 0

23 October 2012 427.1557

23 October 2012 861.6443

23 October 2012 960.4365

23 October 2012 1011.052

24 October 2012 1029.391

24 October 2012 1028.815

24 October 2012 1017.831

24 October 2012 975.7554

24 October 2012 905.3704

24 October 2012 730.8137

24 October 2012 65.0983

24 October 2012 0

24 October 2012 430.6326

24 October 2012 849.6389

24 October 2012 951.7253

24 October 2012 996.552

25 October 2012 1032.22

25 October 2012 1038.238

25 October 2012 1024.483

25 October 2012 997.116

25 October 2012 929.6078

25 October 2012 745.0008

25 October 2012 72.10995

25 October 2012 0

25 October 2012 371.8858

25 October 2012 832.3846

25 October 2012 924.6414

25 October 2012 969.1033

26 October 2012 990.9133

26 October 2012 989.9587

26 October 2012 960.4044

26 October 2012 946.488

26 October 2012 872.958

26 October 2012 686.6087

26 October 2012 69.5714

26 October 2012 0

26 October 2012 437.7034

26 October 2012 832.4936

26 October 2012 944.7322

26 October 2012 999.4952

27 October 2012 1027.61

27 October 2012 1031.976

27 October 2012 1024.792

27 October 2012 991.4585

27 October 2012 923.9406

27 October 2012 733.9118

27 October 2012 80.54198

27 October 2012 0

27 October 2012 508.7823

27 October 2012 900.1872

27 October 2012 999.4796

27 October 2012 1043.066

28 October 2012 1064.354

28 October 2012 1064.172

28 October 2012 1047.45

28 October 2012 1016.068

28 October 2012 944.115

28 October 2012 780.8851

28 October 2012 93.38451

28 October 2012 0

28 October 2012 525.7257

28 October 2012 909.8529

28 October 2012 999.5215

28 October 2012 1041.223

29 October 2012 1064.595

29 October 2012 1075.759

29 October 2012 1063.606

29 October 2012 1033.366

29 October 2012 961.7288

29 October 2012 783.5802

29 October 2012 94.15934

29 October 2012 0

29 October 2012 506.4713

29 October 2012 863.7173

29 October 2012 951.2964

29 October 2012 993.0497

30 October 2012 1003.61

30 October 2012 999.9465

30 October 2012 956.7169

30 October 2012 881.2015

30 October 2012 789.0626

30 October 2012 556.7269

30 October 2012 40.79367

30 October 2012 0

30 October 2012 530.0017

30 October 2012 883.8983

30 October 2012 969.1974

30 October 2012 1021.621

31 October 2012 1046.7

31 October 2012 1041.834

31 October 2012 1028.264

31 October 2012 995.0313

31 October 2012 928.0367

31 October 2012 767.3905

31 October 2012 100.1607

31 October 2012 0

31 October 2012 557.325

31 October 2012 905.9581

31 October 2012 990.2995

31 October 2012 1027.673

Answer 4

Anonymous

Do a small change in my formula, instead of "<" change it to "<="

See below bold one.

Then it will tally with your numbers.

=SUMIFS($B:$B,$A:$A,">"&EOMONTH(D2,-1),$A:$A,"<="&EOMONTH(D2,0))

Hope it helps.

Share via

Sumif and left

3 additional answers