SUM, SUMPRODUCT or COUNTIF

[jasonjack]

HI,

I am trying to present some data based on the table below.

I'd like to report for APAC (A) region only 1 instance and a single total of Minutes (C) for SUPPLIER (D).

At the moment using SUMIF and COUNTIFS I get 4 instances of the APAC and 144 Minutes.

Region (G4) Formula is: =COUNTIFS(A:A,"APAC",D:D,"ABC1")

Total Minutes (G5) Formula is: =SUMIFS(C:C,A:A,"APAC",D:D,"ABC1")

Any help would be greatly appreciated.

 

[jasonjack]

ExampleABC.xlsx
	A	B	C	D	E	F	G
1	Region	Country	Minutes	Supplier
2	APAC	Singapore	36	ABC1		What I currently get
3	APAC	Malaysia	36	ABC1		APAC Region	5
4	APAC	Thailand	36	ABC1		Total Minutes	218
5	APAC	Cambodia	36	ABC1
6	AMER	USA	24	ACME		What I want to see
7	AMER	CANADA	24	ACME		APAC Region	2
8	APAC	Singapore	74	ABC1		Total Minutes	110
Sheet1

 

[jasonjack]

Can you confirm whether the count for APAC here would be
1 because APAC Minutes are all 36 or
2 because there are two separate sections (row groups) with APAC?

jasonjack.xlsm
	A	B	C
1	Region		Minutes
2	APAC		36
3	APAC		36
4	AMER		24
5	AMER		24
6	APAC		36
7	APAC		36
Sheet1

APAC count would be 2 and Minutes would be 110.

 

[Peter_SSs]

How do you get 110 Minutes?

 

[jasonjack]

How do you get 110 Minutes?

Apologies Peter, prob not explaining myself clearly enough (by product of a stroke back in 2019).

Based on the data......there should be a count of 2 for APAC and 110 Minutes, 1 count of 36 and 1 count of 74.

Jay

ExampleABC.xlsx
	A	B	C	D	E	F	G
1	Region	Country	Minutes	Supplier
2	APAC	Singapore	36	ABC1		What I currently get
3	APAC	Malaysia	36	ABC1		APAC Region	5
4	APAC	Thailand	36	ABC1		Total Minutes	218
5	APAC	Cambodia	36	ABC1
6	AMER	USA	24	ACME		What I want to see
7	AMER	CANADA	24	ACME		APAC Region	2
8	APAC	Singapore	74	ABC1		Total Minutes	110
Sheet1

 

[Peter_SSs]

.. but for my example in post #10 for APAC, is it
- a count of 1 with Minutes total = 36, or
- a count of 2 with Minutes total = 72, or
- something else?

 

[jasonjack]

.. but for my example in post #10 for APAC, is it
- a count of 1 with Minutes total = 36, or
- a count of 2 with Minutes total = 72, or
- something else?

it's a count of 2 with Minutes total 110 (36 + 74)

 

[Peter_SSs]

it's a count of 2 with Minutes total 110 (36 + 74)

Sorry, I cannot see how you get 74 anywhere from my sample data in post #10. Can you spell that out for me?

 

[jasonjack]

Sorry, I cannot see how you get 74 anywhere from my sample data in post #10. Can you spell that out for me?

ExampleABC.xlsx
	A	B	C	D	E	F	G
1	Region	Country	Minutes	Supplier
2	APAC	Singapore	36	ABC1		What I currently get
3	APAC	Malaysia	36	ABC1		APAC Region	5
4	APAC	Thailand	36	ABC1		Total Minutes	218
5	APAC	Cambodia	36	ABC1
6	AMER	USA	24	ACME		What I want to see
7	AMER	CANADA	24	ACME		APAC Region	2
8	APAC	Singapore	74	ABC1		Total Minutes	110
Sheet1

Line8 is APAC and 74 mins.

 

[Peter_SSs]

I'm asking about my sample data in post #10. It is different to your sample data.

 

[jasonjack]

I'm asking about my sample data in post #10. It is different to your sample data.

It's my sample data I'm looking for a solution to. Post #18