Match data, count data and report to diffrent sheet.

[George W. W.]

I have a work sheet that I want to report to another page. I am putting
togehter a production tracking tool, and need to pull information from two
diffren colums in the work sheet and count them. Basically what I want is to
be able to if v2:V690 = 8386 and r2:r690 = 1/20/09 then count 1 for every
time it happes in the same row. I've tried this several diffrent was if
then, count, sum, what am I doing wrong and how do I do it right. this was
the last effort

=COUNTIFS(IF(LCR!V2:V690,B4),(LCR!R2:R690,G3))
Please help!

 

[Shane Devenshire]

Hi,

v2:V690 = 8386 and r2:r690 = 1/20/09

=SUMPRODUCT(--(V2:V690=A1),--(R2:R690=B1))

And in A1 enter 8386 and in B1 enter 1/20/09

 

[francis]

I don't have 2007 version to test for COUNTIFS
but you can try this

=SUMPRODUCT(--(LCR!V2:V690=B4),--(LCR!R2:R690=G3))

this assume that B4 hold the value 8386 and G3 hold the date 1/20/09
the date format need to be the same as your data
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[francis]

Further to my post, I am assuming that you want this in another sheet
hence the LCR!. If this is not the case, the formula can be shorten to just

=SUMPRODUCT(--(V2:V690=B4),--(R2:R690=G3))
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[George W. W.]

Shane & Francis,

I appreciate you intrest in helping with this problem. I tried the formulas
and it is still not working properly, it now always reports as 0 and should
actually be reporting as 4 in this instance. Any other ideas?

 

[francis]

Hi George

I have tested the formula before I posted and its work fine.
Are your date format consistent? meaning that both the dataset
and B1 should be formatted as MM/DD/YY format

Do you have other data in your range?
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked


Thank You

cheers, francis

 

[George W. W.]

yes, I have multiple welder numbers 15 diffrent number. 8386 is one of 15
possible numbers. the date formats are the same on both work sheets. I've
been working on this forever and it still isn't working. any other ideas,
please help.

 

[George W. W.]

Hey,

I finally figured it out if any one else runs into this try this formula
=SUMPRODUCT((LCR!$V$2:$V$690=$B4)*(LCR!$R$2:$R$690=C$3))

not only does it work but you can drag it across and then down to populate
your data fields. Thanks Francis and Devenshire you guys put me on the right
track.