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.

 

[Peter_SSs]

I'm not sure what you are asking so if you can clarify that further it would be good.

In any case, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

 

[jasonjack]

I'm not sure what you are asking so if you can clarify that further it would be good.

In any case, I suggest that you update your Account details (or click your user name at the top right of the forum) so helpers always know what Excel version(s) & platform(s) you are using as the best solution often varies by version. (Don’t forget to scroll down & ‘Save’)

Thanks Peter. Updated.

I'm hoping the following screenshot will make it clearer.

I basically have a sheet of data (Column A > D) and I'll looking to avoid double, triple, etc. of data.

 

[Peter_SSs]

Do you want APAC Region to be 1 because
- all suppliers are the same , or because
- all suppliers and minutes are the same, or because
- something else?

 

[Peter_SSs]

BTW, thanks for updating your profile and I suggest that you investigate XL2BB for providing sample data to make it easier for helpers by not having to manually type out sample data to test with.

 

[jasonjack]

Do you want APAC Region to be 1 because
- all suppliers are the same , or because
- all suppliers and minutes are the same, or because
- something else?

I want APAC to be 1 and Minutes to be 36 regardless of how many there would be for APAC and 36 Minutes. The Supplier would be irrelevant tbh.

Hope that helps.

Jay

 

[jasonjack]

It's probably worth adding........there would be other instances of APAC with different Minutes which I also want to count/sum:

 

[Peter_SSs]

I want APAC to be 1 .... regardless of how many there would be for APAC and 36 Minutes.

Then why do you need a formula if the result is to be 1 no matter what?

 

[Peter_SSs]

Then why do you need a formula if the result is to be 1 no matter what?

OK, I think you just answered that.
The result for APAC is the count of different Minute amounts?

 

[Peter_SSs]

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