SumIf formula with multiple criteria from different tab

usnapoleon · Apr 2, 2024

I'm beating my head on this one, everything looks right to me so I could use another eye to see what I'm missing.

Here is raw data tab:

WRSD CC Reconciliation Template.xlsx

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

47

48

Amex

Discover

MC/V

49

Canyon Grille

Market Place

Pizza Hut

The Shop

Canyon Grille

Market Place

Pizza Hut

The Shop

Canyon Grille

Market Place

Pizza Hut

The Shop

50

51

52

53

Test

Cell Formulas
Range	Formula
A50:A53,C50:D53	A50	=A9
B50:B53	B50	=B9+E9+F9+H9
E50:E53	E50	=G9
G50:G53	G50	=J9+M9+N9+P9
H50:I53	H50	=K9
J50:J53	J50	=O9
L50:L53	L50	=R9+U9+V9+X9+Z9+AC9+AD9+AF9
M50:N53	M50	=S9+AA9
O50:O53	O50	=X9+AF9

it actually goes down for 31 days, which is row 80, I only did the first few days to give you the general idea. The CC Names (Amex, Discover, MC/V) are in merged cells, I dont know if it matters. I tried unmerging and it didnt affect things.

Here is where I want the data to go. This is my AMEX tab, I have another tab for Discover and another for Visa/MC. So whatever we can accomplish here, will help me on the others.

WRSD CC Reconciliation Template.xlsx

A

B

C

D

E

F

6

Raw Data Code:

7

Amex

8

Date Posted

Canyon Grille

Market Place

Pizza Hut

The Shop

9

1-Mar

0.00

10

11

12

13

AMX

Cell Formulas
Range	Formula
B9	B9	=IFERROR(SUMIFS(Test!B50:O80,Test!A50:A80,$A$9,Test!48:48,$A7,Test!$49:$49,B$8),0)

It's meant to identify certain criteria before delivering the result:

The Raw Data Code on the amex tab's A7 to the code in row 48 of the Test tab
The outlet name on the amex tab's row 8 to the name in row 49 of the Test tab
The date on the amex tab's column A to the date in column A of the Test tab

=IFERROR(SUMIFS(Test!B50:O80,Test!A50:A80,$A$9,Test!48:48,$A7,Test!$49:$49,B$8),0)

Not sure where to fix.

myall_blues · Apr 3, 2024

Your SUMIFS is not working because the range arguments have different numbers of rows and columns. Per the Microsoft documentation:

Use the same number of rows and columns for range arguments.

The Criteria_range argument must contain the same number of rows and columns as the Sum_range argument.

One alternative is using SUMPRODUCT instead:
In B9: =SUMPRODUCT(($A$7=Test!$B$48)*($A9=Test!$A50:$A80)*(B$8=Test!B$49:E$49)*Test!$B$50:$E$80)

Regards

Murray

usnapoleon · Apr 3, 2024

myall_blues said:
Your SUMIFS is not working because the range arguments have different numbers of rows and columns. Per the Microsoft documentation:

Use the same number of rows and columns for range arguments. The Criteria_range argument must contain the same number of rows and columns as the Sum_range argument.

One alternative is using SUMPRODUCT instead:
In B9: =SUMPRODUCT(($A$7=Test!$B$48)*($A9=Test!$A50:$A80)*(B$8=Test!B$49:E$49)*Test!$B$50:$E$80)

Regards

Murray

I didnt know that, thank you! I tried your code and it worked perfectly. Thank you for that. I've not used sumproduct much. I was using Index originally, then I was turned onto SumIf, and now I see this. Cool stuff, thank you

Alex Blakenburg · Apr 3, 2024

if you are happy to rely on the order of Canyon Grill, Market Place, Pizz Hut, The Shop being consistent across all sections then the formula below should work across all columns.

20240403 SumIfs multiple dimensions usnapoleon.xlsx