Copilot for Microsoft 365 Tech Accelerator
Feb 28 2024 07:00 AM - Feb 29 2024 10:30 AM (PST)
Microsoft Tech Community
LIVE

Need help executing large volume of text formulas

Copper Contributor

Screenshot 2023-10-30 215940.pngScreenshot 2023-10-30 212755.pngThis is a combination of needlessly complex formula turned into text by coping and pasting as values but i need all of the formulas to be executed (452 rows to be exact) but it excel won't accept it unless i double click individual cell and then enter. Is there any easier way to do it?

4 Replies
="=SUMIF(C9:C649,F"&ROWS(C$25:C49)&",D9:D649)" i turned the result of this into text for context
P.S i just learned a tiny bit of form yt and experimented and is my 1st time needing to turn a long **bleep** list of sales then separate individual members and add their total purchases to turn them into points 1 point per 100 unit

@Dumb_Fork 

Change the formula in the selected cell to

 

=SUMIF($C$9:$C$649,F175,$D$9:$D$649)

 

and press Ctrl+Enter. You should see the result.

Then double-click the fill handle (the little square) in the lower right corner of the cell.

they are individual calculations with different end values as seen above the selected cell.
the above values are the results after double clicking the cells and entering

@Dumb_Fork 

SUMIF works with array formulas (dynamic arrays in modern Excel or CSE in legacy Excel), in which case the formula you show is no more than

= SUMIF(party, party, netAmnt)

where 'party' and 'netAmnt' are defined names applied to your ranges.