SUMIFS formula

[RodneyC]

This formula works: =SUMIFS(Numbers!F5:F8,Numbers!E5:E8,ReportCalculator!B20) I realize it only has one criteria to look at, but I'm taking this one step at a time.

What I need to add to this is for it to look in Numbers!F4:G4 and look for the value in ReportCalculator!$B$2

When I change this formula =SUMIFS(Numbers!F5:F8,Numbers!E5:E8,ReportCalculator!B20) to this: =SUMIFS(Numbers!F5:F8,Numbers!E5:E8,ReportCalculator!B20,Numbers!F4:G4,ReportCalculator!B2) I get a #VALUE! error.

The value in B2 is a fliter value I can select a date from that needs to change the data in the cell I'm looking at based on it's value.

Any ideas? thanks in advance

 

[Gimics]

Hey Rodney - any chance you can provide a screenshot or example?

It looks like you have misaligned range sizes - you're trying to lookup a value in a row for your second condition, but a value in a column for your first condition with your actual sum range just being a column.

If you're trying to lookup rows and columns, a better formula might be an =INDEX() formula using the =MATCH() formula to find the right rows and columns, or the use of =SUMPRODUCT() formula. There's a ton of info on the forum or on Google to help with either of these.

 

[RodneyC]

I think you're right, one series is in a column and another in a row. Since I'm early in the creation of this, I'll try and switch one of them to see if that works. Otherwise, I'll look into Index but the key is it needs to trigger off the pivot table data. thanks for the direction.

 

[DanteAmor]

Try this:

varios 01mar2023.xlsm
	A				E	F	G	H	I
1
2
3
4						zs	xd
5					a	5	3		12
6					b	6	2
7					a	7	4
8					a	8	5
Numbers

Cell Formulas
Range	Formula
I5	I5	=SUMPRODUCT((Numbers!E5:E8=ReportCalculator!B20)*(Numbers!F4:G4=ReportCalculator!B22)*(Numbers!F5:G8))

varios 01mar2023.xlsm
	A	B
1
19
20		a
21
22		xd
ReportCalculator

 

[Peter_SSs]

Which sheet is the formula on?

 

[RodneyC]

I got it figured out. Gimics helped point me in the right direction. I simply needed to place all the data in columns and it worked perfectly. Thank you everyone for your input. This is what ended up working: =SUMIFS(Numbers!$F:$F,Numbers!$E:$E,ReportCalculator!$B20,Numbers!$D:$D,ReportCalculator!$B$2)

 

[Peter_SSs]

Glad you got it sorted.

As a matter of interest, what is the answer to my question?

 

[RodneyC]

Glad you got it sorted.

As a matter of interest, what is the answer to my question?

ReportCalculator

 

[Peter_SSs]

ReportCalculator

Thanks, I was suspecting an answer like that.
Can I suggest then that you change (shorten) your new formula to

=SUMIFS(Numbers!$F:$F,Numbers!$E:$E,$B20,Numbers!$D:$D,$B$2)

The reason is that it is not only unnecessary to use that worksheet name in the formula, but it can actually lead to incorrect formula results in some circumstances.

 

[RodneyC]

Thanks, I was suspecting an answer like that.
Can I suggest then that you change (shorten) your new formula to

=SUMIFS(Numbers!$F:$F,Numbers!$E:$E,$B20,Numbers!$D:$D,$B$2)

The reason is that it is not only unnecessary to use that worksheet name in the formula, but it can actually lead to incorrect formula results in some circumstances.

Thanks for the knowledge.