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

 

[SamJW]

Hi Rick,

This seems like a great idea, attempting this now the internet has decided to come back! Here's hoping I don't mess it up.

Many thanks,

SamJW

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.

 

[Aladin Akyurek]

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,

Data...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Date[/TD]
[TD]Invoice[/TD]
[TD]Net[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

7/24/2015​

[/TD]
[TD]JS PLC[/TD]
[TD]

3500​

[/TD]
[TD]

11​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

4/21/2015​

[/TD]
[TD]MJ LTD[/TD]
[TD]

6700​

[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

11/1/2014​

[/TD]
[TD]JW & Co.[/TD]
[TD]

38575​

[/TD]
[TD]

44​

[/TD]
[/TR]
</tbody>[/TABLE]


Processing...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Category[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

8​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

11​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

44​

[/TD]
[TD]

38575​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


B3, copied across and down:

=SUMPRODUCT(Sheet1!$C$2:$C$4,--(Sheet1!$D$2:$D$4=$A3),--(TEXT(Sheet1!$A$2:$A$4,"mmm")=B$1))

 

[SamJW]

Hi Aladin,

Thank you for that formula, struggling with it somewhat however. I tried entering it manually, replacing Sheet 1 for the name of the sheet of course, and then copying and pasting it and locating 'Sheet 1' with windows explorer - both to no avail?

Also tried replacing the '$C2:$C4' parts to '$C:$C' and it still isn't working - a little lost !

Many thanks,
SamJW

Data...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD]Date[/TD]
[TD]Invoice[/TD]
[TD]Net[/TD]
[TD]Category[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]

7/24/2015​

[/TD]
[TD]JS PLC[/TD]
[TD]

3500​

[/TD]
[TD]

11​

[/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

4/21/2015​

[/TD]
[TD]MJ LTD[/TD]
[TD]

6700​

[/TD]
[TD]

8​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

11/1/2014​

[/TD]
[TD]JW & Co.[/TD]
[TD]

38575​

[/TD]
[TD]

44​

[/TD]
[/TR]
</tbody>[/TABLE]


Processing...

[TABLE="class: grid"]
<tbody>[TR]
[TD]Row\Col[/TD]
[TD]

A​

[/TD]
[TD]

B​

[/TD]
[TD]

C​

[/TD]
[TD]

D​

[/TD]
[TD]

E​

[/TD]
[TD]

F​

[/TD]
[/TR]
[TR]
[TD]

1​

[/TD]
[TD][/TD]
[TD]NOV[/TD]
[TD]DEC[/TD]
[TD]JAN[/TD]
[TD]FEB[/TD]
[TD]MAR[/TD]
[/TR]
[TR]
[TD]

2​

[/TD]
[TD]Category[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]

3​

[/TD]
[TD]

8​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

4​

[/TD]
[TD]

11​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

5​

[/TD]
[TD]

44​

[/TD]
[TD]

38575​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[TD]

0​

[/TD]
[/TR]
[TR]
[TD]

6​

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


B3, copied across and down:

=SUMPRODUCT(Sheet1!$C$2:$C$4,--(Sheet1!$D$2:$D$4=$A3),--(TEXT(Sheet1!$A$2:$A$4,"mmm")=B$1))

 

[Aladin Akyurek]

Hi Aladin,

Thank you for that formula, struggling with it somewhat however. I tried entering it manually, replacing Sheet 1 for the name of the sheet of course, and then copying and pasting it and locating 'Sheet 1' with windows explorer - both to no avail?

Also tried replacing the '$C2:$C4' parts to '$C:$C' and it still isn't working - a little lost !

Many thanks,
SamJW

See the workbook that implements the set up: https://dl.dropboxusercontent.com/u/65698317/SamJW SumProduct.xlsx.

Note. The whole column references in such formulas are avoided for reasons of efficiency.

 

[SamJW]

Hi Aladin,

Thank you for the workbook, I see how you've implemented it. Again copying it across to my data set doesn't seem to be working - getting £0.00 across the board in 'Sheet2'.

Just to mention in case it makes a difference, my actual 'Sheet1' has 378 rows of data, and some invoices have the same category? Not sure if that would make a difference?

Many thanks,

SamJW

See the workbook that implements the set up: https://dl.dropboxusercontent.com/u/65698317/SamJW SumProduct.xlsx.

Note. The whole column references in such formulas are avoided for reasons of efficiency.

 

[Aladin Akyurek]

Hi Aladin,

Thank you for the workbook, I see how you've implemented it. Again copying it across to my data set doesn't seem to be working - getting £0.00 across the board in 'Sheet2'.

Just to mention in case it makes a difference, my actual 'Sheet1' has 378 rows of data, and some invoices have the same category? Not sure if that would make a difference?

Many thanks,

SamJW

The range to sum must be true numbers, not text numbers.

=ISNUMBER(C2)

in the data sheet must yield TRUE.