Replacement to SUMIFS

[rhjake23]

Hello.

I am trying to use if statements and/or sumproduct instead of sumifs. When using sumifs, my macros bog down.....
I am trying the following:

=IF(AND((AT2:AT1000=AT2,AW2:AW1000=AW2)),SUMPRODUCT(AV2:AV1000))

Basically, if the range of at2:at1000 = the value in AT2 and if the range of aw2:aw1000 = the value in AW2, sum up the values in the range av2:av1000.

Any input would be fantastic. Thank you.

 

[Scott Huish]

All those pieces should be in the SUMPRODUCT:

=SUMPRODUCT(--(AT2:AT1000=A2),--(AW2:AW1000=AW2),AV2:AV1000)

 

[rhjake23]

Thank you for the quick reply !!! Do I still use the IF AND ? in place of the dashes or ?

 

[rhjake23]

Thanks again. I got it to work but the sumproduct function totally slows down the excel marco. I need to figure this out with just IF statements.

 

[Scott Huish]

No, you don't need the IF or the AND, but it's not that big of a range to slow it down that much. And how does a spreadsheet formula relate to your macro exactly?

 

[rhjake23]

I run a api call macro which runs just fine if the excel file doesn't have all the sumifs and/or sumproduct functions. Once I add those functions within the excel file the macro totally slows down and takes approx. 5 times longer. Wondering if (if statements as an array or something) would work.

 

[J.Ty.]

Make a test: change recalculation to manual, run the macro, and then switch to automatic again.

 

[DanteAmor]

Put your macros here to review them, maybe "sumproduct" calculations can be included in the macro, then the macro will return to the sheet only the calculated values. That is, performing calculations on the sheet slows down operations, so the ideal is to perform all calculations in memory.

Also put a sample of your data with which your macros would work.

And in which cells you are putting the formula, only in one or a range of cells.

 

[rhjake23]

I will try that...

Application.Calculation =​

xlManual...then

Application.Calculation = xlAutomatic at the end of the macro. Thank you!​