SUM, SUMPRODUCT or COUNTIF

jasonjack

New Member
Joined
Apr 30, 2014
Messages
26
Office Version
  1. 365
Platform
  1. MacOS
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.

1621587516068.png
 

Attachments

  • 1621586804668.png
    1621586804668.png
    13 KB · Views: 9
ExampleABC.xlsx
ABCDEFG
1RegionCountryMinutesSupplier
2APACSingapore36ABC1What I currently get
3APACMalaysia36ABC1APAC Region5
4APACThailand36ABC1Total Minutes218
5APACCambodia36ABC1
6AMERUSA24ACMEWhat I want to see
7AMERCANADA24ACMEAPAC Region2
8APACSingapore74ABC1Total Minutes110
Sheet1
 
Upvote 0

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.
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
ABC
1RegionMinutes
2APAC36
3APAC36
4AMER24
5AMER24
6APAC36
7APAC36
Sheet1
APAC count would be 2 and Minutes would be 110.
 
Upvote 0
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
ABCDEFG
1RegionCountryMinutesSupplier
2APACSingapore36ABC1What I currently get
3APACMalaysia36ABC1APAC Region5
4APACThailand36ABC1Total Minutes218
5APACCambodia36ABC1
6AMERUSA24ACMEWhat I want to see
7AMERCANADA24ACMEAPAC Region2
8APACSingapore74ABC1Total Minutes110
Sheet1
 
Upvote 0
.. 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?
 
Upvote 0
.. 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)
 
Upvote 0
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
ABCDEFG
1RegionCountryMinutesSupplier
2APACSingapore36ABC1What I currently get
3APACMalaysia36ABC1APAC Region5
4APACThailand36ABC1Total Minutes218
5APACCambodia36ABC1
6AMERUSA24ACMEWhat I want to see
7AMERCANADA24ACMEAPAC Region2
8APACSingapore74ABC1Total Minutes110
Sheet1


Line8 is APAC and 74 mins.
 
Upvote 0
I'm asking about my sample data in post #10. It is different to your sample data.
 
Upvote 0

Forum statistics

Threads
1,223,516
Messages
6,172,777
Members
452,477
Latest member
DigDug2024

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top