Replace Pivot table with sumifs

[Bulloff]

Hello community,

Is there a way to replicate the results of a pivot table with sumifs for example. The issue is that the particular pivot table has some items filtered in report filter and I do not know how to handle this in a formula.

I have a report with say 30 columns and 20 000 rows. The columns I am interested are :
A- Region
B- Sales
C- Product
Other columns are not important for the sake of this question.
In a pivot table I can put a filter on product for specific products and then get total sales per region and specific products.
How can I do this with formulas? How can I exlcude specific products in the formula with sumifs?

Thanks for any hints

 

[Bulloff]

Anyone?

Tnx

 

[MARZIOTULLIO]

Can you provide some sample data and what the expected outcome is?

 

[Bulloff]

Can you provide some sample data and what the expected outcome is?

Yes,
Below is the result with a pivot table: I have filtered all products without A in the pivot table. How can I get the same result with a formula, not using a Pivot table? Without this additional filter set on Product I can do it with SUMIF, but do not know how to exclude Product A and use SUMIF together.

					Product	(Multiple Items)
	Region	Product	Sales
	East	A	10		Row Labels	Sum of Sales
	West	B	20		East	50
	North	C	30		North	90
	South	D	40		South	110
	East	E	50		West	20
	North	F	60		Grand Total	270
	South	G	70
Thanks.

<colgroup><col span="5"><col><col><col></colgroup><tbody>
</tbody>

 

[MARZIOTULLIO]

	A​	B​	C​	D​	E​	F​
1​	Region	Product	Sales
2​	East	A	10​		Row Labels	Sum of Sales
3​	West	B	20​		East	50​
4​	North	C	30​		North	90​
5​	South	D	40​		South	110​
6​	East	E	50​		West	20​
7​	North	F	60​		Grand Total	270​
8​	South	G	70​

<tbody>
</tbody>

f3=SUMIFS($C$2:$C$8,$B$2:$B$8,"<>"&$B$2,$A$2:$A$8,$E3) copy down until west

f7=SUM(F3:F6)

 

[C with no eyes]

	A​	B​	C​	D​	E​	F​
1​	Region	Product	Sales
2​	East	A	10​		Row Labels	Sum of Sales
3​	West	B	20​		East	50​
4​	North	C	30​		North	90​
5​	South	D	40​		South	110​
6​	East	E	50​		West	20​
7​	North	F	60​		Grand Total	270​
8​	South	G	70​

<tbody>
</tbody>

f3=SUMIFS($C$2:$C$8,$B$2:$B$8,"<>"&$B$2,$A$2:$A$8,$E3) copy down until west

f7=SUM(F3:F6)

This does work with this particular data set but only because product A happens to be in B2.

Assuming the same layout as marz, this would account for the aforementioned drawback.

f3=SUMIFS(C:C,A:A,"="&E3,B:B,"<>a")

 

[Bulloff]

	A​	B​	C​	D​	E​	F​
1​	Region	Product	Sales
2​	East	A	10​		Row Labels	Sum of Sales
3​	West	B	20​		East	50​
4​	North	C	30​		North	90​
5​	South	D	40​		South	110​
6​	East	E	50​		West	20​
7​	North	F	60​		Grand Total	270​
8​	South	G	70​

<tbody>
</tbody>

f3=SUMIFS($C$2:$C$8,$B$2:$B$8,"<>"&$B$2,$A$2:$A$8,$E3) copy down until west

f7=SUM(F3:F6)

Hi , thanks for replying. However I am unable to get it right for some reason:

SUMIFS($C$3:$C$9,$B$3:$B$9,B3,$C$3:C10,"<>A") - this returns #Value error.

Where am I doing it wrong, the first condition is easy, but I do not know how to exclude the "A" product, not being a specific cell but a text that will be repeated many times over a long list.
I think I do not know how to formulate "excluding" and that`s why the error comes.

Thanks.

 

[C with no eyes]

Hello, please see my post above.

 

[Bulloff]

Hello, please see my post above.

Hi, thanks it worked.
One question. why do you use "=" &E3? Ir works also with E3 as a criteria?

Thanks

 

[C with no eyes]

Because I had a different formula in there first and forgot to take it out when I amended it. It does not need to be there.