SUMIF with MATCH - 2 variables

[SamJW]

Hi all,

This is my first post so please excuse any irregularities with the following.

I am working on a document with two worksheets. On the first worksheet the data looks as so (not real values):

[TABLE="width: 500"]
<tbody>[TR]
[TD]Date[/TD]
[TD]Invoice[/TD]
[TD]Net[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]24/07/2015[/TD]
[TD]JS PLC[/TD]
[TD]£3500[/TD]
[TD]11[/TD]
[/TR]
[TR]
[TD]21/04/2015[/TD]
[TD]MJ LTD[/TD]
[TD]£6700[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]01/11/2014[/TD]
[TD]JW & Co.[/TD]
[TD]£38575[/TD]
[TD]44[/TD]
[/TR]
[TR]
[TD]Etc.[/TD]
[TD]Etc.[/TD]
[TD]Etc.[/TD]
[TD]Etc.[/TD]
[/TR]
</tbody>[/TABLE]


On the second worksheet it looks as so:

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MARCH[/TD]
[TD]Etc...[/TD]
[/TR]
[TR]
[TD]Category[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD]X[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Etc...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Essentially, I am struggling on how to match the SUMIF function with the MATCH (I think) function where I need to SUMIF in the gaps where the X's are on the second worksheet, given that the invoices are from the correct month and the correct category. I hope this makes sense?

If anyone could help me here I would be truly truly grateful!

Kind regards,

SamJW

 

[Special-K99]

For this to work based on the sample data and the explanation you've given you are implying that there are no duplicates in the row of month names in the second sheet, e.g. NOV only occurs once in that row.
Is that correct?

Secondly the month names should all be three characters long, NOV DEC JAN FEB MAR, not NOV DEC JAN FEB MARCH

 

[123rickfear]

if you add an extra column onto the end of sheet 1 with the formula =month(A:A), and an extra row in row 1 of sheet 2 with the month numbers (November is 11, Jan is 1 etc), then you can put the below formula in.

=SUMIFS(Sheet1!$C:$C,Sheet1!$D:$D,Sheet2!$A4,Sheet1!$E:$E,Sheet2!B$1) and drag.

 

[Special-K99]

With NOV in Sheet2!B1 this should work, no extra columns/rows needed

in Sheet2!B3 (two rows below Nov)
=SUMPRODUCT((Sheet1!D2:D4=A3)*(MONTH(Sheet1!A2:A4)=B3)*(Sheet1!C23:C4))

Copy across and down

 

[Aladin Akyurek]

@SamJW

Are those 'months', that is, NOV, etc. of 2014 or of 2015?

 

[SamJW]

Thank you for your reply! Internet has only just come back up, the issues of working from home in a rural area...

They are indeed months, November through to March in the example above

@SamJW

Are those 'months', that is, NOV, etc. of 2014 or of 2015?

 

[SamJW]

Thanks for your quick reply! Internet has only just come back (trouble with working from home in a rural area ). In sheet 2 the NOV is a column heading, where the data from the previous sheet needs to be added.

Apologies, in my sheet it is MAR not MARCH, I typed it incorrectly

For this to work based on the sample data and the explanation you've given you are implying that there are no duplicates in the row of month names in the second sheet, e.g. NOV only occurs once in that row.
Is that correct?

Secondly the month names should all be three characters long, NOV DEC JAN FEB MAR, not NOV DEC JAN FEB MARCH

 

[Aladin Akyurek]

Thank you for your reply! Internet has only just come back up, the issues of working from home in a rural area...

They are indeed months, November through to March in the example above

Is NOV the month name or a date (i.e., 1-Nov-15), formatted to display as NOV?

 

[SamJW]

Hi Special-K99, this looks really promising, but it seems to have a circular reference in there? In the middle of the formula you have " *(MONTH(Sheet1!A2:A4)=B3) " is that B3 not referring to the cell I am entering the formula into?

Thanks,

SamJW

With NOV in Sheet2!B1 this should work, no extra columns/rows needed

in Sheet2!B3 (two rows below Nov)
=SUMPRODUCT((Sheet1!D2:D4=A3)*(MONTH(Sheet1!A2:A4)=B3)*(Sheet1!C23:C4))

Copy across and down

 

[SamJW]

Hi Aladin,

NOV is just a heading on the second worksheet, it isn't formatted to a date, its a heading meaning to cover all invoices from that month found in sheet 1,

Is NOV the month name or a date (i.e., 1-Nov-15), formatted to display as NOV?