Sumif multiple rows, single criteria

C

c_m_s_jr

Well-known Member

Joined

Mar 23, 2009

Messages

1,561

• May 4, 2009

• #2

Have you considered just adding the two together? Meaning if you are trying to sum what is in column B and column C (bothing having to meet the same criteria) then the formula would look like:

=SUMIF(criteria range,"criteria",B2:B100)+SUMIF(criteria range, "criteria",C2:C100).

Hope I interpeted your problem correctly.

 

Upvote 0

jbeaucaire

Well-known Member

Joined

May 8, 2002

Messages

6,012

• May 4, 2009

• #3

Maybe this:

=SUMPRODUCT(($D$14:$BS$14=D$368)*($D184:$BS185))

 

Upvote 0

D

dviper

New Member

Joined

Sep 20, 2003

Messages

30

• May 4, 2009

• #4

Actually, this was just a short example from a problem of hundreds of different rows, columns, sumif formulas, but thanks anyway.

 

Upvote 0

D

dviper

New Member

Joined

Sep 20, 2003

Messages

30

• May 4, 2009

• #5

Sumproduct works. Thank you very much

 

Upvote 0

R

radrichard

New Member

Joined

Oct 25, 2016

Messages

1

• Oct 25, 2016

• #6

jbeaucaire said:

Maybe this:

=SUMPRODUCT(($D$14:$BS$14=D$368)*($D184:$BS185))

Click to expand...

Sorry for digging up this old post but please could someone explain why this works?

 

Upvote 0

jbeaucaire

Well-known Member

Joined

May 8, 2002

Messages

6,012

• Nov 4, 2016

• #7

Easiest way is to SEE it working. Adjust your formula to a smaller range, say 10-15 rows only. Then select the cell and go to the FORMULAS > EVALUATE FORMULA tool. Now, you can step through the formula one calculation at a time and watch it unfold.

 

Upvote 0

D

deepfunk9

New Member

Joined

May 10, 2013

Messages

5

• Feb 2, 2022

• #8

This video explains how the sumproduct works in the scenario you are asking about.

 

Upvote 0

Joe4

MrExcel MVP, Junior Admin

Joined

Aug 1, 2002

Messages

72,239

Office Version

1. 365

Platform

1. Windows

• Feb 2, 2022

• #9

deepfunk9 said:

This video explains how the sumproduct works in the scenario you are asking about.

Click to expand...

The last post was made to this thread over 5 years ago, and the last questioner hasn't been back since then!

 

Upvote 0

D

deepfunk9

New Member

Joined

May 10, 2013

Messages

5

• Feb 2, 2022

• #10

Joe4 said:

The last post was made to this thread over 5 years ago, and the last questioner hasn't been back since then!

Click to expand...

OK. But I'm sure other people will have the same problem. I did and that is how I found this thread. I found this thread helpful and thought other people might find the video useful as well as it gives a good explanation.

 

Upvote 0