Sumifs formula with recurring values

[macangc]

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

 

[jasonb75]

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]

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

 

[jasonb75]

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]

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]

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.