Problem with SUMIF

[Ozwilly]

Hi,

I have a workbook where I track expenses against a number of allocated codes. It is a date progressive worksheet, and I need a way to determine a YTD total based on how I filter the list. I have attached a scaled down version of the model. If you look at the 'A&P Tracking' tab you will see a range of expenses entered in by date, allocated to a brand, assigned to a cost code and subcategory, and allocated to a supplier and a cost entered for that expense.

On the 'Summary' tab I have a list of the brands, and the expense categories along the top. Codes relating to these categories are below this summary table. I am having trouble finding a way to do an index match on column D of the 'A&P Tracking' tab to identify the brand in the Summary table, and then to look up the respective expense category code / name from column E of F of the 'A&P' tab, and then to do a SUMIF of some sort on column K of this tab to give a YTD total.

As an example, if I filter the 'A&P' tab on column D to show 'Brand A' and on column E to show 'Markdowns' the filtered list will return a value at the top of column K of $98,862. At the moment if I want to find out what the total expense for Markdown for Brand A is for the full year, I have to do this manually in this manner. Similarly if I filter the list by date to see only July, I can do so however have to do it manually.

What I am therefore trying to achieve is on the Summary tab, to have a formula that in this case would look up Brand A on the 'A&P' tab, cross reference with Markdown, and return a total value in that cell. If I further filter the 'A&P' tab to show say selected months, I would like to also be able to build in the option of the formula being able to look at the months selected and return that value.

Trust that makes sense????

Thanks in advance

[Cutter]

Maybe something like this:

=SUMIFS('A & P Tracking'!K:K,'A & P Tracking'!D:D,"Brand A",'A & P Tracking'!E:E,"Markdowns",'A & P Tracking'!C:C,"July")

replace "Brand A", "Markdowns" and "July" with cell refs of cells holding those values

[Ozwilly]

Brilliant thanks, worked a treat

One little thing I did find. I have created two summaries, one being YTD, the other having a dropdown list for month selection. On the YTD one, if I filter the list in the 'A&P' tab, the SUMIFS formula still gives the full YTD total. For example, if I filter out a month it will still show the total with the month included. Is there a way to have it recognize and calculate based on the filtered data set?

[Cutter]

The SUMIFS() won't automatically adapt to the filtering. It can be manipulated somewhat to show what the filtered sheet is showing but why try to get with a formula what you're already getting by filtering in cell K2 of the A&P Tracking sheet.

[Ozwilly]

OK no problem, thanks again. Just to answer your query about why not use the 'A&P' sheet and filter to get specific results. The reason for the query is that this model is used to track expenses, and often I need to do a filtered list that would show for example, expenses to the end of April only (i.e. not showing those that have appeared since the 1st of May). I need to do this in order to reconcile with company records to the end of April in this example.

The formula as it is now is perfect for showing me the full year expenses in 'real time' however in order to only look until the end of the last finished month, I need to go back to the 'A&P' tab and filter each brand and expense type separately, based on capturing all months except the current. I thought if there was some way to filter out all but the current incomplete month, that would be brilliant. Still, a long way ahead of where I was yesterday so half the problem is solved

[Haseeb Avarakkan]

Hi Ozwilly,

See the attached. On Summary enter the reporting year & select reporting month or full year from the drop down.

Does this help you?

[Ozwilly]

Thanks very much. Very close to what I am looking for!!! What I actually want to be able to do is to select two periods that extend beyond one month. In the example you gave, rather than selecting a single month, I would like for example to be able to select January to April, so that the reporting period is 1st January to 30th April. Or to be able to change it when May finishes to be for January to May, or even February to May or whatever selection is desired that I need to report against. Does that make sense???

[Haseeb Avarakkan]

See the attached.

When select a start number in B2, in B3 can see only rest of the month.

[Ozwilly]

Awesome thanks