Help with Formula Vlookup, collect, if?

Not sure if this is possible but I have a sheet to track monthly billing and another sheet to forecast milestone completions.

On the monthly billing sheets I want it to look at the milestone forecasting sheet and if a milestone is forecasted to complete in a month add a billings to the billing sheet.

Billing sheet looks like this:

For Test customer in each monthly Column I want it to look at the milestone sheet and if the project has a milestone dated for the referenced month pull in the correct billing.

This is the Milestone sheet for the customer:

This customer closes phase 3 in may 2023 so in the billing sheet I want to see nothing for Jan - april but in the May column it should pull in $11,512.50, and then in July $9210 so on and so forth and I have no idea where to even start

Find more posts tagged with

Formulas

Accepted answers

All comments

Paul Newcome

If you insert a hidden helper column on the source sheet that pulls the customer name onto every row, you should be able to use a SUMIFS where we grab the amount and match on the customer as well as the year/month within the date.

=SUMIFS({Source Sheet $$ Column}, {Source Sheet Helper Column}, @cell = [Customer Name]@row, {Source Sheet Date Column}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

CDS

Ok so here is the new forecasting sheet:

and here is my formula : =SUMIFS({Forecasting Testing Billings}, {Forecasting Customer Name}, @cell = [Customer Name]@row, {{Forecasting Current Dates}, AND(IFERROR(MONTH(@cell), 0) = 1, IFERROR(YEAR(@cell), 0) = 2023))

Where Forecasting Testing billings = line number column

Forecasting Customer Name = Name Column

Forecasting Current Dates = Current Dates Column

and I get in my billings table:

but I don't want it to add numbers so not sure if I should be summing. In the billings table if the forecasting has a date of 5/26/23 for instance for that customer for May I just want it to pull the corresponding billing in the same row of 11,512.50

Paul Newcome

That particular error comes from a cross sheet reference that was not created properly.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.