Using OFFSET For Dynamic Ranges Along With SUMIFS?

Good afternoon, Gurus! Yet again, I’m not sure if I’m overthinking this, but I’ve picked my brain raw and need some help!

Please see the attached sample data. In Row 1, I’m using OFFSET() to total the values in the Columns as the range of the spreadsheet changes (i.e.: could be 500 rows could be 2,000.) Now, I’d like to use that same OFFSET() logic, but with SUMIFS based on the values in Column A:

For “Flow” (Row 2,) I want to only total the rows where Column A is blank.
For “Program” (Row3,) I want to total any rows where Column A is not blank AND the values is NOT “FF.”
For “Speed” (Row 4,) I want to total any rows where Column A is “FF.”
And for each, I want to maintain the dynamic functionality OFFSET() provides.

Can anyone help, please? Thanks in advance!

(And my apologies; I am about to leave work in a few minutes, so I may not respond/acknowledge any suggestions this evening, but I WILL be back first thing in the morning! Just don't want anyone willing to help to think they're going unappreciated; rest assured reputation is coming your way!)

Originally Posted by JeteMc

Since you are looking for dynamic functionality consider turning the data into a table, as it appears from the file attached to post #1, that is the intent anyway.
If the data is set in an excel table then formulas like the following may be used and they will expand or contract along with the table.

Thanks for the help; this worked like a charm! I REALLY appreciate it! Now, I’m off to present your work as my own and take all the credit! But seriously, this forum continues to be a lifesaver; hopefully one day, you all will imbue me with enough knowledge that I can contribute.

Originally Posted by shukla.ankur281190

ok try

Thanks for the input! I'll save these options for future reference (still new to OFFSET and INDEX!)

Good afternoon, Gurus (JeteMc specifically!)

I’m reopening this thread because the ask for the project has changed a bit and what I thought was a simple adjusting of the formula has put me back in a predicament.

The solution JeteMc offered in his solution worked fine, but the criteria have changed slightly; I’m now trying to identify “Flow” as anything with a VI of “-“, “TR” or “OR”, and any SUMIFs by any of those individual values works, but when I try to SUMIFS using more than one (per the attachment, it returns “0” for Flow. What gives??

Thanks in advance for any help!

Argh, "AND" vs "OR", the crux at 90% of my Excel pinch-points, and I still can't see the forest for the trees; the next iteration of Excel needs a =YOUKNOWWHATIMEAN() function. Alas, given the version I'm currently using, your response makes the kind of "well, DUH" sense I've come to expect. Thanks a ton, yet again, for your help, JeteMC!