Summing figures with duplicate conditions and preset formula

[Excel_noob_calling_help]

Hi,

I'm hoping to get a formula that can help me calculate the answer for F5.
The "Gifts" column has a preset formula/criteria to derive the "1" and "0". So to know the total number of gifts i have put in the formula for F2.
But as each household is only entitled to one gift, i'm hoping to get the total number of gifts for each unique address (there are thousands of different addresses).

Can any kind souls help?

Thank you very much!

 

[bebo021999]

Try COUNTIFS:

=SUM(IF($C$2:$C$10=1,1/COUNTIFS($C$2:$C$10,1,$B$2:$B$10,$B$2:$B$10)))

if C column is textstring (that you are using "1" in for mula, but i guess it is value due to cells alight=right) use "1" in stead of 1

For whom with Ex2016 or older: Confirm with Ctrl-Shift-Enter Combination.

 

[Excel_noob_calling_help]

Try COUNTIFS:

=SUM(IF($C$2:$C$10=1,1/COUNTIFS($C$2:$C$10,1,$B$2:$B$10,$B$2:$B$10)))

if C column is textstring (that you are using "1" in for mula, but i guess it is value due to cells alight=right) use "1" in stead of 1

For whom with Ex2016 or older: Confirm with Ctrl-Shift-Enter Combination.

Hi Thanks for the reply.

However, i use the code with my data and the error message was "#value!".

 

[Excel_noob_calling_help]

And when i use this on the illustrated example given, the answer isnt '3' but its ' 0.33333' too.

Hi Thanks for the reply.

However, i use the code with my data and the error message was "#value!".

 

[bebo021999]

Are you Ex still 2019?
Try with Ctrl-Shift-Enter, in stead of Enter?
If it does not work, send update image (or better, XL2BB) displaying Error again.

 

[Excel_noob_calling_help]

Are you Ex still 2019?
Try with Ctrl-Shift-Enter, in stead of Enter?
If it does not work, send update image (or better, XL2BB) displaying Error again.

Oh Yes! Its the Ctrl-Shift-Enter that makes the difference.
Thank you! its solved =)