Sumifs formula with recurring values

macangc

Board Regular
Joined
Mar 28, 2006
Messages
140
Office Version
  1. 365
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
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Mar 28, 2006
Messages
140
Office Version
  1. 365
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
    Data.PNG
    103.1 KB · Views: 4
  • table.PNG
    table.PNG
    57 KB · Views: 3

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Mar 28, 2006
Messages
140
Office Version
  1. 365
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.


1595407279446.png

1595407454569.png
 

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,986
Office Version
  1. 2019
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,113,903
Messages
5,544,964
Members
410,645
Latest member
aroesch
Top