=SUMIF(‘26 Daily’.G$222:G$374,"<"&B2,‘26 Daily’.B$222:B$374)+SUMIF(‘26 Daily’.G$222:G$374,"<"&B2,‘26 Daily’.E$222:E$374))
I was told I can use SUMPRODUCT instead of multiple SUMIF, but I can’t get it to work.
This formula has been modified and simplified, but I would still like to combine/simplify the 2 SUMIF statements.
Please test with:
=SUMIFS(‘26 Daily’.B$222:B$374~‘26 Daily’.E$222:E$374,‘26 Daily’.G$222:G$374,"<"&B2)
Edit to keep fine the tilde ’
Received an error 508 message on both mariosv & karolus suggestions
Please attach your example file with the formula.
Should have mentioned that the formula is in the electric tab column f. You will notice that tabs are locked so I don’t mess up by accident. No password.
Your file with the formula to test in M2, and criteria in M1
Gas & Electric Billings.ods (860.0 KB)
Also the formula works without the sheet name, being the same sheet where the formula is.
I want to thank you for the formula and your efforts for a stranger. This is the same formula you emailed me and I tried and told you it didn’t work. I don’t know what happened then, but I modified your new working formula to reference the original b2 cell in the electric tab and changed the “>” to a “<” and it worked. I copied it to the electric tab and changed the condition back to just b2, since it was now on the electric tab, and it still works. What I did wrong the 1st time is beyond me, because it should have worked. Thank you for your patience.
Please note that this web site changes plain quotes to topographic quotes (curly quotes which don’t work in Calc) unless the formula is enclosed by back ticks, this is easily done by clicking the preformatted text icon </> and pasting it inside the back ticks
Thanks, EarnestA(?) (not sure if that was a 1, number, or the letter l) that may be why it didn’t work. I didn’t notice the different quotes and didn’t change them. The formula that worked was added to my spreadsheet by mariosv and when I copied that formula it worked becuase it was in the spreadsheet itself, not separate text.
=SUMPRODUCT('26 Daily'.G$222:G$374 < B2;'26 Daily'.B$222:B$374)+SUMPRODUCT('26 Daily'.G$222:G$374<B2;'26 Daily'.E$222:E$374)