SUMIF help

[herpasymplex10]

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
and instead of right(D2,2)="AP"
it would be column (D, 2)="AP"

is this possible?
I've never used SUMIF before

 

[Jonmo1]

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]

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.

 

[Aladin Akyurek]

Are you intending:

=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]

Ignore, Better answers above

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
and instead of right(D2,2)="AP"
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?

 

[herpasymplex10]

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]

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]

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]

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

I think you answered your own question..<!-- / message -->

I was able to come up with the below formula and it works

Glad to help..

 

[herpasymplex10]

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?