# SUMIF help

#### herpasymplex10

##### Board Regular
so I rec'd help on the below code
=IF(AND(OR(F2="F",F2="M"),right(D2,2)="AP"),L2,"")

I have this code in every cell of column M
and then at the bottom of column M I have a cell with the sum of the results
column M produced.

I saw the SUMIF function and was wondering if I could replace all these cells of formulas with just one by turning the above code to a SUMIF?

instead of F2 it would be the column F
it would be column (D, 2)="AP"

is this possible?
I've never used SUMIF before

### Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.

#### Jonmo1

##### MrExcel MVP
Try

=SUMPRODUCT(--(ISNUMBER(MATCH(F1:F100,{"F","M"},0))),--(RIGHT(D1:D100,2)="AP"),L1:L100)

IMPORTANT NOTES
Ranges must be same size
Ranges cannot be entire column refs like A:A, you must included row #s like A1:A100

Hope that helps..

#### DonkeyOte

##### MrExcel MVP
you could use a sumproduct -- am assuming a small range of rows 2:6 but enlarge as necessary.

=SUMPRODUCT(--(RIGHT(\$D\$2:\$D\$6,2)="AP"),--((\$F\$2:\$F\$6="F")+(\$F\$2:\$F\$6="M")),--(\$L\$2:\$L\$6))

EDIT: use jonmo's.

##### MrExcel MVP
Are you intending:
Code:
``````=SUMPRODUCT(
--ISNUMBER(MATCH(\$F\$2:\$F\$100,{"F","M"},0)),
--(LEFT(\$D\$2:\$D\$100,2)="AP"),
\$L\$2:\$L\$100)``````

Prior to Excel 2007, no whole columns are allowed.

#### knocks420

##### New Member

so I rec'd help on the below code
=IF(AND(OR(F2="F",F2="M"),right(D2,2)="AP"),L2,"")

I have this code in every cell of column M
and then at the bottom of column M I have a cell with the sum of the results
column M produced.

I saw the SUMIF function and was wondering if I could replace all these cells of formulas with just one by turning the above code to a SUMIF?

instead of F2 it would be the column F
it would be column (D, 2)="AP"

is this possible?
I've never used SUMIF before

SUMIF is used to calculate the sum of a range based on certain criteria which you specify. It looks like from this example you are looking for specific TEXT such as "L" or "M". Perhaps that represents something else?

Also I believe SUMIF only looks at one range (Column) at a time so you couldnt compare two different ranges and sum a third. There could be a workaround but if it works in its current form why mess with it?

Last edited:

#### herpasymplex10

##### Board Regular
Try

=SUMPRODUCT(--(ISNUMBER(MATCH(F1:F100,{"F","M"},0))),--(RIGHT(D1:D100,2)="AP"),L1:L100)

IMPORTANT NOTES
Ranges must be same size
Ranges cannot be entire column refs like A:A, you must included row #s like A1:A100

Hope that helps..

amazing. thank you.

I'd like to do another.
if F1:F100 = "R"
and H1:H100 = "1011"
then sum G1:G100

i'm trying to work off the above code but am getting stuck
i'd appreciate some help

#### Jonmo1

##### MrExcel MVP
I'd like to do another.
if F1:F100 = "R"
and H1:H100 = "1011"
then sum G1:G100

New formula, or add to previous formula??

Here's the basic syntax for multi criteria sum, you should be able to figure it out..

=SUMPRODUCT(--(Range1=Criteria1),--(Range2=Criteria2),Range2Sum)

Again
Ranges must be same size
Ranges cannot be entire column refs like A:A, you must included row #s like A1:A100

#### herpasymplex10

##### Board Regular
New formula, or add to previous formula??

Here's the basic syntax for multi criteria sum, you should be able to figure it out..

=SUMPRODUCT(--(Range1=Criteria1),--(Range2=Criteria2),Range2Sum)

Again
Ranges must be same size
Ranges cannot be entire column refs like A:A, you must included row #s like A1:A100

Thank you. Your above help was perfect.
I was able to come up with the below formula and it works...thank you so much
=SUMPRODUCT(--(F2:F104="R"),--(H2:H104="1061"),L2:L104)

Do i need to do the CTRL+SHFT+ENTER
on these type of formulas?

#### Jonmo1

##### MrExcel MVP
Do i need to do the CTRL+SHFT+ENTER
on these type of formulas?
I was able to come up with the below formula and it works

#### herpasymplex10

##### Board Regular
Thank you. Your above help was perfect.
I was able to come up with the below formula and it works...thank you so much
=SUMPRODUCT(--(F2:F104="R"),--(H2:H104="1061"),L2:L104)

Do i need to do the CTRL+SHFT+ENTER
on these type of formulas?

Thanks for all the help
Is there a way to make this formula perform as maybe a macro?
What I'd like to do is allow a user to somehow choose
a specific workbook and worksheet to perform this formula on?

Is that possible?

Replies
30
Views
742
Replies
4
Views
500
Replies
2
Views
222
Replies
0
Views
165
Replies
15
Views
862

### Forum statistics

1,191,204
Messages
5,985,263
Members
439,953
Latest member
suchitha ### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?    1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option. Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com". Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button. Go back