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.

 

[Joe4]

It looks to me that for the APAC Region, you want a count of the UNIQUE values in column C, and a total of those unique values.
So even though 36 appears multiple times, you only want one instance of that, and one of 74 minutes.
Is that correct?

 

[Peter_SSs]

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

I understand that, but to establish the logic of how to achieve that, I wanted my questions answered. If you are not able to do that then I will have to leave it to others to assist.

 

[jasonjack]

It looks to me that for the APAC Region, you want a count of the UNIQUE values in column C, and a total of those unique values.
So even though 36 appears multiple times, you only want one instance of that, and one of 74 minutes.
Is that correct?

Thanks Peter, yes that is correct.

 

[Peter_SSs]

Thanks Peter, yes that is correct.

That was Joe, not Peter.

 

[jasonjack]

It looks to me that for the APAC Region, you want a count of the UNIQUE values in column C, and a total of those unique values.
So even though 36 appears multiple times, you only want one instance of that, and one of 74 minutes.
Is that correct?

Yes this is correct.

 

[Joe4]

Yes this is correct.

This gets a bit complicated. But here are some links that show you how to do that:

To get unique counts based on multiple criteria: How to count unique values based on multiple criteria in Excel?
To get sum of unique values based on multiple criteria: How to sum unique values based on criteria in Excel?

 

[jasonjack]

This gets a bit complicated. But here are some links that show you how to do that:

To get unique counts based on multiple criteria: How to count unique values based on multiple criteria in Excel?
To get sum of unique values based on multiple criteria: How to sum unique values based on criteria in Excel?

Thanks Joe + Peter....looks like it worked on the example sheet.....will now test on real data.

 

[Joe4]

Thanks Joe + Peter....looks like it worked on the example sheet.....will now test on real data.

Excellent!
Glad we were able to help you out.

 

[jasonjack]

Thanks Joe + Peter....looks like it worked on the example sheet.....will now test on real data.

So, the SUM Unique values appears to have work.

However, the COUNT Unique numbers is proving a bit tricky.

Can I have more that 2 criteria? Formula is below with current 2. I really could do with 3!

=ArrayFormula(SUM(IF(FREQUENCY(IF('FS - P1 & P2 - Import Data'!AA2:AA600=M4,IF('FS - P1 & P2 - Import Data'!Q2:Q600="Critical (P1)",MATCH('FS - P1 & P2 - Import Data'!J2:J600,'FS - P1 & P2 - Import Data'!J2:J600,0))),ROW('FS - P1 & P2 - Import Data'!J2:J600)-ROW('FS - P1 & P2 - Import Data'!J2)+1),'FS - P1 & P2 - Import Data'!J2:J600)))

Thanks

 

[jasonjack]

So, the SUM Unique values appears to have work.

However, the COUNT Unique numbers is proving a bit tricky.

Can I have more that 2 criteria? Formula is below with current 2. I really could do with 3!

=ArrayFormula(SUM(IF(FREQUENCY(IF('FS - P1 & P2 - Import Data'!AA2:AA600=M4,IF('FS - P1 & P2 - Import Data'!Q2:Q600="Critical (P1)",MATCH('FS - P1 & P2 - Import Data'!J2:J600,'FS - P1 & P2 - Import Data'!J2:J600,0))),ROW('FS - P1 & P2 - Import Data'!J2:J600)-ROW('FS - P1 & P2 - Import Data'!J2)+1),'FS - P1 & P2 - Import Data'!J2:J600)))

Thanks

Excellent!
Glad we were able to help you out.

sorry the ask the wrong way around:

So, the COUNT Unique values appears to have work.

However, the SUM Unique numbers is proving a bit tricky.

Can I have more that 2 criteria? Formula is below with current 2. I really could do with 3!

=ArrayFormula(SUM(IF(FREQUENCY(IF('FS - P1 & P2 - Import Data'!AA2:AA600=M4,IF('FS - P1 & P2 - Import Data'!Q2:Q600="Critical (P1)",MATCH('FS - P1 & P2 - Import Data'!J2:J600,'FS - P1 & P2 - Import Data'!J2:J600,0))),ROW('FS - P1 & P2 - Import Data'!J2:J600)-ROW('FS - P1 & P2 - Import Data'!J2)+1),'FS - P1 & P2 - Import Data'!J2:J600)))