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:

Year	Unique ID	Case No.	Gender	No. of Sales	Total No. of Cases per unique ID
2012	E542	123	Female	2	3
2012	E542	589	Male	1	3
2012	E542	123	Female	2	3
2013	E588	235	Male	3	3
2013	E588	415	Male	1	3
2013	E588	256	Female	2	3

<tbody>
</tbody>

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:

Year	Unique ID	Case No.	Gender	No. of Sales	Total No. of Cases per unique ID
2012	E542	123	Female	2	3
2012	E542	589	Male	1	3
2012	E542	123	Female	2	3
2013	E588	235	Male	3	3
2013	E588	415	Male	1	3
2013	E588	256	Female	2	3

<tbody>
</tbody>

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