Counting Data in Excel
I'm trying to count data based on the value in 2 columns. I'm using the
following formula =COUNTIF(B2:B57,"DLA")--COUNTIF(D2:D57,1) but it is giving me the wrong count. Is there a way I can make this work? Thanks, |
Counting Data in Excel
=sumproduct(--(b2:b57="dla"),--(d2:d57=1))
Adjust the ranges to match--but you can't use whole columns. =sumproduct() likes to work with numbers. The -- stuff changes trues and falses to 1's and 0's. Bob Phillips explains =sumproduct() in much more detail he http://www.xldynamic.com/source/xld.SUMPRODUCT.html And J.E. McGimpsey has some notes at: http://mcgimpsey.com/excel/formulae/doubleneg.html Marilyn wrote: I'm trying to count data based on the value in 2 columns. I'm using the following formula =COUNTIF(B2:B57,"DLA")--COUNTIF(D2:D57,1) but it is giving me the wrong count. Is there a way I can make this work? Thanks, -- Dave Peterson |
Counting Data in Excel
Hi!
Try this: =SUMPRODUCT(--(B2:B57="DLA"),--(D2:D57=1)) Better to use cells to hold the criteria: A1 = DLA A2 = 1 =SUMPRODUCT(--(B2:B57=A1),--(D2:D57=A2)) Biff "Marilyn" wrote in message ... I'm trying to count data based on the value in 2 columns. I'm using the following formula =COUNTIF(B2:B57,"DLA")--COUNTIF(D2:D57,1) but it is giving me the wrong count. Is there a way I can make this work? Thanks, |
Counting Data in Excel
SumProduct works great!!
Thank you ALL!! "Marilyn" wrote: I'm trying to count data based on the value in 2 columns. I'm using the following formula =COUNTIF(B2:B57,"DLA")--COUNTIF(D2:D57,1) but it is giving me the wrong count. Is there a way I can make this work? Thanks, |
All times are GMT +1. The time now is 12:11 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com