SUM unique values based on a criteria

[shaikhrulez]

Hello everyone,

Please refer to attached file.

I tried to sum unique values of column G, which I was able to do with the help of below formula.

=(SUMPRODUCT(1/COUNTIF(G2:G18,G2:G18&""),G2:G18)).

Now, I am trying to sum the same range based on BR_CODE. As soon as the branch codes changes the above range should automatically adjust.

Thanks

 

[Khalid NGO]

Hello Waqar,

I hope someone will offer a formula based solution.

But, I'll go with Pivot Table, if you are okay



I have used a helper column (H) with simple countifs:
=COUNTIFS(B$2:B2,B2,G$2:G2,G2)

See the attached.

Regards,

 

[shaikhrulez]

Hello Waqar,

I hope someone will offer a formula based solution.

But, I'll go with Pivot Table, if you are okay

View attachment 28030

I have used a helper column (H) with simple countifs:
=COUNTIFS(B$2:B2,B2,G$2:G2,G2)

See the attached.

Regards,

Great Khalid,

Although your solution serves the purpose, but formula solution would be much more dynamic and easy to use when data changes.

Is there anyone who can come up with the formula based solution?

 

[santoshbhagade]

Hi @shaikhrulez

Please check the attached file if this is what you are looking at

 

[shaikhrulez]

Hi @shaikhrulez

Please check the attached file if this is what you are looking at

Santosh, thanks for replying. I guess you hv missed something. As I am trying to sum unique values in column G and you have summed up values of column F.

Kindly look again. Thanks

 

[santoshbhagade]

@shaikhrulez
please check the attached file.

 

[shaikhrulez]

@shaikhrulez
please check the attached file.

This one works fine Thanks a lot buddy Indeed a good solution.

 

[santoshbhagade]

cheers

 

[bosco_yip]

Sum range based on unique BR_CODE, one formula without helper way,

H2, formula copy down :

=IF(B2=B1,"",SUMPRODUCT((B$2:B$1033=B2)/COUNTIFS(B$2:B$1033,B$2:B$1033&"",G$2:G$1033,G$2:G$1033&""),G$2:G$1033))

Regards
Bosco

 

[santoshbhagade]

you nailed it @bosco_yip

 

[bines53]

Hello friends,

Another one,
=IF(B2=B1,"",SUM(MMULT(--($B$2:$B$1034=B2)*SIGN(FREQUENCY($G$2:$G$1033,$G$2:$G$1033))*$G$2:$G$1034,1))),
Just make sure you need to add another row, highlighted in red, that this version will be faster than COUNTIFS .

David

 

[Khalid NGO]

Hi David,
Nice use of frequency.

Please cross check, I have found different results for few entries, for example H33, H362, H509, H557 ...... H957

Regards,

 

[bosco_yip]

Or try this FREQUENCY function version,

=IF(B2=B1,"",SUM((FREQUENCY(IF(B$2:B$1034=B2,MATCH(G$2:G$1033,G$2:G$1033,0)),MATCH(G$2:G$1033,G$2:G$1033,0))>0)*$G$2:$G$1034))

This is an array formula, confirm enter with SHIFT+CTRL+ENTER

Regards
Bosco

 

[bines53]

Hi @Khalid NGO,

Yes I have seen, there is the G column, values are repeated in different BR_CODE, I'll try to find a trick to avoid it.

David
http://forum.chandoo.org/members/khalid-ngo.15425/

 

[bines53]

Hello friends,
The revised formula and accurate,
=IF(B2=B1,"",SUM(SIGN(FREQUENCY(IF($B$2:$B$1033=B2,$G$2:$G$1033,0),IF($B$2:$B$1033=B2,$G$2:$G$1033,0)))*($B$2:$B$1034=B2)*$G$2:$G$1034))

Just make sure you need to add another row, highlighted in red.
This is an array formula, confirm enter with SHIFT+CTRL+ENTER

There is of course the easiest way,
=SUM(UNIQUEVALUES(IF($B$2:$B$1033=B2,$G$2:$G$1033)))
with SHIFT+CTRL+ENTER
UNIQUEVALUES----Morefunc add in.

This is just an example, one function of many others missing in Excel.

David

 

[bosco_yip]

Or try this FREQUENCY function version,

=IF(B2=B1,"",SUM((FREQUENCY(IF(B$2:B$1034=B2,MATCH(G$2:G$1033,G$2:G$1033,0)),MATCH(G$2:G$1033,G$2:G$1033,0))>0)*$G$2:$G$1034))

This is an array formula, confirm enter with SHIFT+CTRL+ENTER

Regards
Bosco

Another version of FREQUENCY, all in the same range without add another row,

=IF(B2=B1,"",SUM(IF(FREQUENCY(IF(B$2:B$1033=B2,MATCH(G$2:G$1033,G$2:G$1033,0)),ROW(B$2:B$1033)-ROW(B$2)+1),G$2:G$1033)))

This is an array formula, confirm enter with SHIFT+CTRL+ENTER

Regards
Bosco