Best formula to use to extract data - SUMIFS ?

[Oldwestern]

Hi,
I received help from the group some weeks ago suggesting I use SUMIFS to group and calculate household bills that trying to analyse.

I've attached a sample Excel file.

Although I'm a relative newbie to Excel, I feel what I'm trying to achieve is pretty basic stuff, but I'm running into problems.

I'm using Excel For Mac v16.29.1 on an iMac 2011 generation, running High Sierra 10.13.6

The Bills are entered in a random date order. New 'Suppliers', 'Categories' and 'Source'* are added to columns A & B respectively in a separate Data! sheet which are used to make a drop down tables (*'Source' being a code describing how the bill was paid and by whom).

From the randomly entered Bills I want to find totals by various date ranges ( Yr, Monthly, Weekly) for each 'Category', 'Supplier' and & 'Source'.

I entered a test SUMIFS code at K3 to extract from the sample Bills any bill for 2017 that met the Category criteria of M2.

I found that apart from not working, I realise that I would need to hand code all the date ranges rather than use cell references. But there is an even more basic problem: how can I create a code that I don't need to update to reference the range of bills - i.e. the first is always going to be at Row 3, but I've no idea where it will end, indeed the point is it's meant to be on-going.

I hope ya'll will be able to understand what I'm trying to achieve and can give some help.

Many thanks.
Hal.

[CARACALLA]

M3=SUMIFS($E$3:$E$100,$A$3:$A$100,">="&$I3,$A$3:$A$100,"<="&$J3,$F$3:$F$100,M$2)

copy across and down

[WideBoyDixon]

Or:

Please Login or Register  to view this content.

Not sure which is more efficient but they both will work. Note you'll need to adjust the 3:20 range to capture all the bills you enter.

WBD

[Oldwestern]

Thank you both Caracalla and WideBoyDixon for your formulas. They work for the 'Category' section, but not if I extend the fill to 'Suppliers' and 'Source".

There are also errors in how it works in both the monthly and weekly sections.

Further, if I understand you both correctly, it appears that the vertical range (i.e. the number of ALL bills entered, would have to be updated to reflect any new bills entered, which would be tedious, if not impossible.

I'm thinking there must be a way to count the number of row to which the entered bills extend, so that the row-range to be specified would reside in two cells - the start row cell & a cell which counts or automatically states the last row with data in it, for each of Cols:A.B.D,E & F...if you see what I mean.

I'd like to find a a way that automatically sets the range for each of the calculation ares (Categories, Suppliers Source etc) or I guess the only other way would be to set a very large range to begin with of say 5,000 rows

I attache the file with WideBoys formula inserted and some extra bill data added, including a wild one at row 20

[CARACALLA]

Updated formulas

Check the file

[Oldwestern]

Hi Caracalla,

Thanks for the very swift response. Yes, that works fine. I did check over the previous formula and I must have done something wrong somewhere in filling the rows and columns.

Thanks you for your patient and excellent help.

Kind regards,

Hal