Returning a count/sum based on mulitple criteria

[AliG999]

First time poster

I hope you can help. I have data which is broken down per year for which I need to return a count against each unique ID but in which there may be multiple rows linked to that ID but I would like a unique count and in some cases a sum of and want to avoid duplicate counts. the data looks something like below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD]Unique ID[/TD]
[TD]Case No.[/TD]
[TD]Gender[/TD]
[TD]No. of Sales[/TD]
[TD]Total No. of Cases per unique ID[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]E542[/TD]
[TD]123[/TD]
[TD]Female[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]E542[/TD]
[TD]589[/TD]
[TD]Male[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]E542[/TD]
[TD]123[/TD]
[TD]Female[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]E588[/TD]
[TD]235[/TD]
[TD]Male[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]E588[/TD]
[TD]415[/TD]
[TD]Male[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]E588[/TD]
[TD]256[/TD]
[TD]Female[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


For e.g. I would like to count for 2012 how many females/males per unique ID (to avoid duplication in counting the same individuals more than once)

Or I may wish to sum how many sales per unique ID for each year.

I have tried all sorts of COUNT IF/S or SUM/IFs variations but cannot figure it out - any help would be gratefully appreciated.

thanks

Ali

 

[AliG999]

Bump

 

[vane0326]

First time poster

I hope you can help. I have data which is broken down per year for which I need to return a count against each unique ID but in which there may be multiple rows linked to that ID but I would like a unique count and in some cases a sum of and want to avoid duplicate counts. the data looks something like below:

[TABLE="class: grid, width: 500"]
<tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Year[/TD]
[TD]Unique ID[/TD]
[TD]Case No.[/TD]
[TD]Gender[/TD]
[TD]No. of Sales[/TD]
[TD]Total No. of Cases per unique ID[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]E542[/TD]
[TD]123[/TD]
[TD]Female[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]E542[/TD]
[TD]589[/TD]
[TD]Male[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2012[/TD]
[TD]E542[/TD]
[TD]123[/TD]
[TD]Female[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]E588[/TD]
[TD]235[/TD]
[TD]Male[/TD]
[TD]3[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]E588[/TD]
[TD]415[/TD]
[TD]Male[/TD]
[TD]1[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]2013[/TD]
[TD]E588[/TD]
[TD]256[/TD]
[TD]Female[/TD]
[TD]2[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]


For e.g. I would like to count for 2012 how many females/males per unique ID (to avoid duplication in counting the same individuals more than once)

Or I may wish to sum how many sales per unique ID for each year.

I have tried all sorts of COUNT IF/S or SUM/IFs variations but cannot figure it out - any help would be gratefully appreciated.

thanks

Ali

Assuming the Columns ranges are from A to F

=SUMPRODUCT(--($A$2:$A$7=2012),--(MATCH($B$2:$B$7&$D$2:$D$7,$B$2:$B$7&$D$2:$D$7,0)=ROW(INDEX($B$2:$B$7,0,0))-ROW($A$2)+1))

 

[AliG999]

Thanks Vane

I've used the formula you suggested on my actual datasheet =SUMPRODUCT(--('TELECOMS REQUESTS'!$A$3:$A24=2012),--(MATCH($B$3:$B$24&$AE$3:$AE$24,$B$3:$B$24&$AE$3:$AE$24,0)=ROW(INDEX($B$3:$B24,0,0))-ROW($A$3)+1))

The only thing is it returns a value of 7 however when I do a check using a pivot table it should be 9 - have I done something wrong?

 

[AliG999]

Apologies Vane

It did work I just forgot to add in the sheet name for the ranges - dafty! Been a long day trying to figure this out. Can I ask for an idiots explanation of the above formula?

Thanks again for all your help

ali