Sumifs formula with recurring values

macangc

Board Regular
Hi

I have a sumifs formula that calculate values based on value, date & supplier (named ranges as per below).

=SUMIFS(AMOUNT,SUPPLIER1,\$A24,DATE,">="&C\$23,DATE,"<="&EOMONTH(C\$23,0))

This works for when the row providing the data is a one off value but in another column (named range - Occurence) has a value of 0 its one off but it says 1 it should be every month, if it says 12 it should be every 12 months. At them moment i have to input the item the number of times the value occurs for.

For example i have microsoft office and payment happens monthly so in "Occurance" column it says 12 and so i would like that value to appear for the next 12 months rather than having to enter 12 rows with the 12 months manually

thanks in advance, hope you can help

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

jasonb75

Well-known Member
Not sure if I'm visualising it correctly, but I think that you might just need to change the 0 in EOMONTH(C\$23,0) with the relevant occurrence.

macangc

Board Regular
Not sure if I'm visualising it correctly, but I think that you might just need to change the 0 in EOMONTH(C\$23,0) with the relevant occurrence.
Thanks for the reply. That didnt work unfortunately. i think maybe because the data that the formula pulls from is another table which has multiple lines for different amounts,suppliers & dates. Ive added pictures of the data entry sheet and the table where the formula is. Hopefully that helps

thanks again though

Attachments

• Data.PNG
103.1 KB · Views: 5
• table.PNG
57 KB · Views: 4

jasonb75

Well-known Member
Ive added pictures of the data entry sheet and the table where the formula is.
Please use XL2BB for that (click the button on the reply toolbar and follow the instructions), not screen captures.

A screen capture shows the data, but doesn't show a lot of other equally important information (formulas, named range references). Even the references to \$A24 and C\$23 are left to assumption.

It also helps if you provide the results that you expect based on the example provided.

Also, please update your profile to show the correct version of excel that you use (click your user name at the top right of this page, then 'Account details', scroll down and check the correct box, then scroll to the bottom and save changes).

macangc

Board Regular
Thanks unfortunately im blocked from doing that by admins, not sure if it helps but ive done another pic which shows the formula and another which displays the data source info a bit more. If you cant though, i appreciate you taking the time to respond. Ive updated my excel version also, using 365.

jasonb75

Well-known Member
For example i have microsoft office and payment happens monthly so in "Occurance" column it says 12 and so i would like that value to appear for the next 12 months rather than having to enter 12 rows with the 12 months manually
This appears to conflict with what I see in the screen captures above, am I right in thinking that Monthly and 12 should be repeated for 12 columns, but Monthly and 1 should only appear once?

I don't think that this will be possible with just the formulas in the cashflow sheet, it will most likely need additional column(s) in the data source to assist with identifying the months that the repeat amount should be posted to.

Identifying duplicates is going to be near to (if not completely) impossible, so a monthly amount that is set to appear for 12 months would be duplicated if you did happen to enter it in the data source a second time.

Replies
6
Views
404
Replies
12
Views
362
Replies
6
Views
374
Replies
19
Views
331
Replies
2
Views
110

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,499
Messages
5,832,068
Members
430,109
Latest member
tinezi

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back