Sum of two counts

**scottnshelly** · 12-22-2010, 11:38 AM

hello,

i'm having difficulty with this formula. in column L i have a list of identification numbers including some multiples. in column F is "yes" or "no" depending. i'm trying to create a summary of how many No's appear for each identification number on a different sheet. i hope this is enough information, let me know if i need to elaborate more.

Thanks,

**Blake 7** · 12-22-2010, 11:44 AM

Can you post a sample worksheet? sounds like you need a Countif formula.

**NBVC** · 12-22-2010, 11:47 AM

Have a try with Pivot tables:

http://peltiertech.com/Excel/Pivots/pivotstart.htm

**Blake 7** · 12-22-2010, 12:37 PM

Hi N - Ive been trying to work out how to do this without a PT just for my own benefit. It looks like a blend of sumif and countif!
please could you put me out of my misery!

thanks alot.

**NBVC** · 12-22-2010, 01:40 PM

See attached.

Just drag Id and Yes/No columns to Row Area

Then drag Yes/No columns also to Data Area (this should give Count of Yes/No)

You can then filter the Yes/No Row area column to show only the No counts.

**Blake 7** · 12-22-2010, 01:54 PM

Hi N, sorry if I was not clear in my post... what I meant to say was, ive been trying to work out how to do it WITHOUT using a PT, ie, with a formula.

Sorry for not being clear. Thanks alot mate

**NBVC** · 12-22-2010, 02:03 PM

Sorry, it was my fault. I misread your post.

See attached.

I added a helper column to find uniques...

In C2:

=IF(COUNTIF(A$2:A2,A2)=1,COUNT(C$1:C1)+1,"")

copied down

then in D2:

=MAX(C:C) to get total count

Then to list the unique ids:

In H2, I entered":

=IF(ROWS($A$1:$A1)>$D$2,"",INDEX($A$2:$A$19,MATCH(ROWS($A$1:$A1),$C$2:$C$19)))

copied down

and in I2 to count the "No" entries:

=IF(H2="","",SUMPRODUCT(--($A$2:$A$19=H2),--($B$2:$B$19="NO")))

if you have 2007

=IF(H2="","",COUNTIFS($A$2:$A$19,H2,$B$2:$B$19,"NO"))