0

I'm going to try my best to explain what I'm looking for.

I have travel data that identifies charges incurred by a person. In this dataset I also have the days in advance that the flight was booked I want to create a formula that determines what amount of their total spend was used for flights booked less than 14 days in advance. Below is a sample set. The actual set is a much larger file.

In this example, I would have a total spend of 4029.79, 2075.02 of which is booked less than 14 days in advance. I want to do this with a formula. Ultimately what I want to do is put this information into a stacked bar graph.

enter image description here

enter image description here

EDIT** Got the formula down but unable to add it as a pivot table calculated field(see picture). Please help

Jose
  • 5
  • 2
  • 3
    Does this answer your question? [Excel SUMIF between dates](https://stackoverflow.com/questions/14057010/excel-sumif-between-dates) – bad_coder Dec 21 '19 at 05:18
  • With your data you could use something like `=SUMIF(Table1[Days Advance Purchase],"<"&14,Table1[Air Charge])` However, if you also need to take into account the names then `=SUMIFS(Table1[Air Charge],Table1[Name],"SMITH/MIKE",Table1[Days Advance Purchase],"<"&14)`. – shrivallabha.redij Dec 21 '19 at 05:26
  • so =SUMIF(Air[days advance purchase],"<14",Air[air charge]) returns the total that is ,14 but how do I return it based on the passenger name as well? – Jose Dec 21 '19 at 05:28
  • scrapped the pivot and made it into a table. Thank you all! – Jose Dec 21 '19 at 06:36

1 Answers1

0

enter image description here

You mean something like that? SUMMEWENNS is the German equivalent of SUMIFS. The Capital S is no missspelling.

Milan Pijanovic
  • 67
  • 6