Row Count With Matching Cell Criteria

I need a formula that will count the number of rows (or times) in which
a cell has a date and the other has a certain tank number list. For
example the list in column A has tank numbers like the following:
6-125-4-V
6-120-5-J
8-14-2-V
6-12-3-F
8-12-2-J
In column D, most of the cells are blank until the tank is opened and a
date is entered.
I want to count the number of J tanks that have a date entered, the
number of V tanks that have a date entered, and etc. The closest I have
gotten is with a sumproduct formula but I can't figure out how to match
the certain tank letters with each date entered.
I tried some of this, but none is working:
=SUMPRODUCT((RIGHT(A9:A1611,2)="-J")*(M9:M1611<>""))
=SUMPRODUCT((A9:A1611="-J")*(M9:M1611<>""))
=SUMPRODUCT(ISTEXT(FIND("J",A9:A1611))*(M9:M1611<>""))
=SUMPRODUCT(COUNTIF(A9:A1611,"*-J*")*(M9:M1611<>""))
Any assistance would be greatly appreciated.

0
darrelll (5)
11/6/2005 7:41:39 PM
excel 39880 articles. 2 followers. Follow

6 Replies
508 Views

Similar Articles

[PageSpeed] 27

You mention dates in Column D in your question, but use Column M in your
formula examples.

Let's use D for dates.

In E9 to E11 enter:
V, J, F

Then in F9, enter this formula:

=SUMPRODUCT((RIGHT($A$9:$A$1611)=E9)*(ISNUMBER($D$9:$D$1611)))

And copy down to F11.

You should now have your number of open tanks next to the tank ID.
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Lankchevy" <darrelll@earl-ind.com> wrote in message
news:1131306099.890615.270340@z14g2000cwz.googlegroups.com...
> I need a formula that will count the number of rows (or times) in which
> a cell has a date and the other has a certain tank number list. For
> example the list in column A has tank numbers like the following:
> 6-125-4-V
> 6-120-5-J
> 8-14-2-V
> 6-12-3-F
> 8-12-2-J
> In column D, most of the cells are blank until the tank is opened and a
> date is entered.
> I want to count the number of J tanks that have a date entered, the
> number of V tanks that have a date entered, and etc. The closest I have
> gotten is with a sumproduct formula but I can't figure out how to match
> the certain tank letters with each date entered.
> I tried some of this, but none is working:
> =SUMPRODUCT((RIGHT(A9:A1611,2)="-J")*(M9:M1611<>""))
> =SUMPRODUCT((A9:A1611="-J")*(M9:M1611<>""))
> =SUMPRODUCT(ISTEXT(FIND("J",A9:A1611))*(M9:M1611<>""))
> =SUMPRODUCT(COUNTIF(A9:A1611,"*-J*")*(M9:M1611<>""))
> Any assistance would be greatly appreciated.
>

0
ragdyer1 (4059)
11/6/2005 7:57:38 PM
Actually, looking more closely at *your* formulas, some should have worked.

This leads me to think that perhaps your tank ID data is not as it appears.
Perhaps you might have "hidden" characters or spaces.

If my first suggestion doesn't work, try this one:

=SUMPRODUCT((RIGHT(TRIM($A$9:$A$1611))=E9)*(ISNUMBER($D$9:$D$1611)))

If the above formula doesn't work either, try manually entering some tank
ID's, where you *know* that there is no hidden characters.
-- 
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"Ragdyer" <RagDyer@cutoutmsn.com> wrote in message
news:eR7pbxw4FHA.1000@tk2msftngp13.phx.gbl...
> You mention dates in Column D in your question, but use Column M in your
> formula examples.
>
> Let's use D for dates.
>
> In E9 to E11 enter:
> V, J, F
>
> Then in F9, enter this formula:
>
> =SUMPRODUCT((RIGHT($A$9:$A$1611)=E9)*(ISNUMBER($D$9:$D$1611)))
>
> And copy down to F11.
>
> You should now have your number of open tanks next to the tank ID.
> -- 
> HTH,
>
> RD
>
> --------------------------------------------------------------------------
-
> Please keep all correspondence within the NewsGroup, so all may benefit !
> --------------------------------------------------------------------------
-
> "Lankchevy" <darrelll@earl-ind.com> wrote in message
> news:1131306099.890615.270340@z14g2000cwz.googlegroups.com...
> > I need a formula that will count the number of rows (or times) in which
> > a cell has a date and the other has a certain tank number list. For
> > example the list in column A has tank numbers like the following:
> > 6-125-4-V
> > 6-120-5-J
> > 8-14-2-V
> > 6-12-3-F
> > 8-12-2-J
> > In column D, most of the cells are blank until the tank is opened and a
> > date is entered.
> > I want to count the number of J tanks that have a date entered, the
> > number of V tanks that have a date entered, and etc. The closest I have
> > gotten is with a sumproduct formula but I can't figure out how to match
> > the certain tank letters with each date entered.
> > I tried some of this, but none is working:
> > =SUMPRODUCT((RIGHT(A9:A1611,2)="-J")*(M9:M1611<>""))
> > =SUMPRODUCT((A9:A1611="-J")*(M9:M1611<>""))
> > =SUMPRODUCT(ISTEXT(FIND("J",A9:A1611))*(M9:M1611<>""))
> > =SUMPRODUCT(COUNTIF(A9:A1611,"*-J*")*(M9:M1611<>""))
> > Any assistance would be greatly appreciated.
> >
>

0
ragdyer1 (4059)
11/6/2005 8:16:48 PM
The first one should do it

=SUMPRODUCT((RIGHT(A9:A1611,2)="-J")*(M9:M1611<>""))

what was wrong with that one.

-- 

HTH

RP
(remove nothere from the email address if mailing direct)


"Lankchevy" <darrelll@earl-ind.com> wrote in message
news:1131306099.890615.270340@z14g2000cwz.googlegroups.com...
> I need a formula that will count the number of rows (or times) in which
> a cell has a date and the other has a certain tank number list. For
> example the list in column A has tank numbers like the following:
> 6-125-4-V
> 6-120-5-J
> 8-14-2-V
> 6-12-3-F
> 8-12-2-J
> In column D, most of the cells are blank until the tank is opened and a
> date is entered.
> I want to count the number of J tanks that have a date entered, the
> number of V tanks that have a date entered, and etc. The closest I have
> gotten is with a sumproduct formula but I can't figure out how to match
> the certain tank letters with each date entered.
> I tried some of this, but none is working:
> =SUMPRODUCT((RIGHT(A9:A1611,2)="-J")*(M9:M1611<>""))
> =SUMPRODUCT((A9:A1611="-J")*(M9:M1611<>""))
> =SUMPRODUCT(ISTEXT(FIND("J",A9:A1611))*(M9:M1611<>""))
> =SUMPRODUCT(COUNTIF(A9:A1611,"*-J*")*(M9:M1611<>""))
> Any assistance would be greatly appreciated.
>


0
bob.phillips1 (6510)
11/6/2005 8:34:03 PM
I'm sorry about the column D that I mentioned. I have my dates in
column M. I tried what you suggested, but the way I have it setup, it's
not working. The SUMPRODUCT(('Tank Chart'!A9:A1611="-J")*('Tank
Chart'!M9:M1611<>"")) worked if I took out the tank number and used the
word "test1" instead of "J" (putting test in place of the tank#). The
problem is it's not picking out the "J" from the tank number. When I
use COUNTIF(A9:A1611,"*-J*") to count the total number of J tanks, it
works, but it's not working for the sumproduct formula.

0
darrelll (5)
11/6/2005 9:00:41 PM
=SUMPRODUCT(--(ISNUMBER(SEARCH("-J",'Tank Chart'!A9:A1611))),--('Tank 
Chart'!M9:M1611<>""))

-- 
Regards,

Peo Sjoblom

(No private emails please)


"Lankchevy" <darrelll@earl-ind.com> wrote in message 
news:1131310841.691967.114090@f14g2000cwb.googlegroups.com...
> I'm sorry about the column D that I mentioned. I have my dates in
> column M. I tried what you suggested, but the way I have it setup, it's
> not working. The SUMPRODUCT(('Tank Chart'!A9:A1611="-J")*('Tank
> Chart'!M9:M1611<>"")) worked if I took out the tank number and used the
> word "test1" instead of "J" (putting test in place of the tank#). The
> problem is it's not picking out the "J" from the tank number. When I
> use COUNTIF(A9:A1611,"*-J*") to count the total number of J tanks, it
> works, but it's not working for the sumproduct formula.
> 

0
terre081 (3245)
11/6/2005 9:09:54 PM
Peo,
It works like a champ.
Thanks everyone for the help.
  Darrell (Lankchevy)

0
darrelll (5)
11/6/2005 11:27:25 PM
Reply:

Similar Artilces:

Hiding blank rows
I have a spreadsheet with columns, first name, last name, home phone, business phone and cell phone. When I don't have phone information in any of the three columns I would like that column to be hidden or deleted. Can I do this in some automated fashion? Thanks. Michael try adding a column in that column put something like =if(counta(b2-d2)>0,"",1) and use autofilter to hide the 1s "mlkpied" wrote: > I have a spreadsheet with columns, first name, last name, home phone, > business phone and cell phone. When I don't have phone information in any of ...

Can you lock a drop-down cell so that it sorts with the row?
I have created drop-down cells (type of business) for my client contact list but when I sort that list alphabetically or by last contacted date the drop-down cells don't sort with it. How can I solve that issue? Thanks for the responses! Are these Data Validation dropdown lists? Where is the list range located? i.e. =$A$2:$A$20 If inside your sort area the lists should change to whatever is in A2:A20 after the sort. Tested in 2003 and 2007 Gord Dibben MS Excel MVP On Tue, 26 Jan 2010 13:19:01 -0800, Lisa in Victoria <Lisa in Victoria@discussions.micros...

Create static text from cell reference
Hey everyone... I have two columns of text which I'm combining in a third column using the formula (for C1, for example) =A1 & char(10) & B1 This gives me the contents of A1 on a line above the contents of B1 and works fine. What I NEED to do is somehow create column C as TEXT, not as a REFERENCED data from columns A and B. How do I create a cell that contains the actual TEXT content of another cell instead of a REFERENCE to the other cell? TIA... Select all the cells in "C" that have content. R-click them and select "Copy" then r-click again, sele...

Copy cell to a new cell #2
In Column B I have certain values which start with GB2 e.g GB2-02210. I would to like to copy these values and paste it in another cell. I would like to do this with only values which start with GB2. Many thanks -- Message posted via http://www.officekb.com I would use Data>Filter>Autofilter and use custom>begins with... -- HTH Nick Hodge Microsoft MVP - Excel Southampton, England www.nickhodge.co.uk nick_hodgeTAKETHISOUT@zen.co.uk.ANDTHIS "mohd21uk via OfficeKB.com" <u20517@uwe> wrote in message news:5ede27c5beb13@uwe... > In Column B I have certain values ...

making all formulas appear in the cells instead of values
Hi all, How do I make all the existing formulas in a worksheet appear on it, instead of the resulting values? I opened a worksheet that have looks this way, but I don't know how it was done. Thanks in advance, Alejandro I found it out! tools/options/view/formulas Thx! -- Alejandro Caballero Aste Ministerio de Econom�a y Finanzas Direcci�n General de Asuntos de Econom�a Internacional, Competencia e Inversi�n Privada Tlf 311-5930 Axo 3536 ecaballero@mef.gob.pe "Alejandro Caballero Aste" <ecaballero@mef.gob.pe> wrote in message news:%23qJrSVgVHHA.4796@TK2MSFTNGP05...

Macro for merging rows
I have a fairly large spreadsheet that are sorted based on a file # (ie: E0800100, E0800101). The spreadsheet is setup to where each entry is on an individual row as seen below: A B C E0800100 Review.... 1.0 (hr) E0800100 Review.... 2.0 E0800101 Review.... 1.5 E0800102 Review.... .5 I am trying to organize the spreadsheet so that there is only one row per file number and the Descriptions (B) and Time (C) extend along the columns of that row. A. B....

To find the Last row
The following is the pattern of the data I have. I need to know which is the last row which has the data A1 - AAA A2- BBB A3 - <blank> A4 - CCC A5 - DDD A6 - <Blank> A7 - <Blank> A8 - <Blank> A9 - EEE. In the above example I want to get the row number as 9 as the last row which has the data. Can this be acheived by some formula. I tried COUNTA, but it does not serve my purpose. Please help. Thanks Anand ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.Excel...

looking for empty row to paste a range of copied cells
Hi - I am trying to make a code that will copy a range ("A27:L27") on sheet 1 and then look for the next empty row on sheet 2 and paste it in range ("A27:L27") . I would also like the macro to insert a new blank row (or insert the copied row) for the purpose of shifting existing SUM functions on sheet 2 down. I would like those functions to be right below the copied/pasted cells every time the macro is executed. Thanks for any help - Jim A You don't Mention What column you want to sum This code will copy and paste to the fist row and then sum column D Sub Cop...

Row Limit in Excel
I work a lot with excel and I know that the row limit is 65,536, but I need more than that. Is there any way to make the number of rows infinite or at least to give me a certain amount of more rows. If there is please let me know. Thank you. -- Please help Molly, that is all there is, per sheet. -- Paul B Always backup your data before trying something new Please post any response to the newsgroups so others can benefit from it Feedback on answers is always appreciated! Using Excel 2002 & 2003 "Molly" <molly_carols@yahoo.com> wrote in message news:FD2C7921-E249-4...

Count / Frequency #3
Hi Aladin, Thank you for assistance and Formulas. Tin� Aladin Akyurek Wrote: > What follows is a bit heavy... > > In B5 enter & copy down: > > =SUMPRODUCT(SUBTOTAL(3,OFFSET($A$5:A5,ROW($A$5:A5)-MIN(ROW($A$5:A5)),,1)),--($A$5:A5=A5)) > > In A3 enter: > > =IF(SUBTOTAL(3,A5:A16)<>COUNTA(A5:A16),MAX(IF(SUBTOTAL(3,OFFSET(A5:A16,ROW(A5:A16)-MIN(ROW(A5:A16)),,1)),B5:B16)),"") > > which must be confirmed with control+shift+enter instead of just with > enter. > > Note 1. I used a bit larger range than your sample range for testing...

Sumif with an array for criteria
Hello, I am trying to write a sum if array formula that will look up against multiple criteria and then finally compare to an array...here is an example of what I wrote: {=SUM(IF(Sheet2!$A$12:$A$10000=Sheet3!$B7,IF(Sheet2!$C$12:$C$10000=(Lots!$H$6:$H$18),Sheet2!$K$12:$K$10000,0),0))} The formula works fine if I take out the $H$18, but as soon as I try to match that, it will not work... So to clarify what I am trying to do: Sheet 2 contains my data: Column A is weeks - Column K is Sales - (what I want to sum) Column C is SKU ID Sheet 3 is the Form I am summing to. Cel...

Can I control linked cell property value in a copied ActiveX contr
I need to copy an ActiveX control approximately 200 times in 1 worksheet (not form). The LinkedCell property value does not change when I copy and paste - so I end up with 200 controls pointing to the same cell. This is a pain to edit. The control is invoked with the following syntax =EMBED("Forms.ScrollBar.1",""). It "smells like I should be able to expose that cell address so a copy might work but I can't find any reference to the EMBED on line or in 6 books. Any help or redirection would be much appreciated. ...

Row colouring that accommodates filtering?
What can be used instead of "=MOD(ROW(),2)=1" that accommodates filtering, pls? The above gives clumps of identically-coloured rows, dependent on the particular filter criteria used. Thank you! :oD As long as there are no empty cells within the filtered list: Assume A1:B1 is the header row. A2:B10 is the data Select the range A2:B10 Conditional Formatting Formula Is: =MOD(SUBTOTAL(3,$A1:$A$2),2)=0 Biff "StargateFanFromWork" <NoSpam@NoJunk.com> wrote in message news:%23wKPl8LmHHA.4768@TK2MSFTNGP05.phx.gbl... > What can be used instead of "=MOD(ROW...

Insert row with formatting from row below instead of row above?
My default Excel template has the top row formatted as a header, with bold text and a dark bottom border to distinguish it from the rows below. So if I insert a row at row 2, the new row is formatted like the header. Then I have to select the new row and remove the bold font, remove the dark border, select the header row and reapply the bottom border. Is there a way I can change the insert default so that it takes it's formatting from the row below instead of the row above. Or better yet, so that it has no formatting at all? Hi AFAIK you can't change this behaviour -- Regards Fra...

Copying Interest formula to 214 cells.
I am doing a project for work and need to copy a interest formula to th next 214 months (Ipmt function). Ii am receiving an error when I try t fill the remind cells. Is there a way to copy that formula? Savings Goal - $750,000 Term - 10 years Interest - 5.5 -- Message posted from http://www.ExcelForum.com Maybe you could post your formula for comments. -- Don Guillett SalesAid Software donaldb@281.com "BradP >" <<BradP.17pbbl@excelforum-nospam.com> wrote in message news:BradP.17pbbl@excelforum-nospam.com... > I am doing a project for work and need to copy...

calculating row by row
I would like to have Excel total the sum of a row 5 of randomly generated numbers in a 6th column, one column at a time. I have no problem generating the random numbers and totalling them but I would like to generate the first number and place it in the total column then when I initiate it generate the second number, add this to the first and place it in the total and so on to the 5th column. Is this possible? Many thanks. Unless I'm missing something here, this formula should do it for you. This is for row 2 on the sheet, and would go into cell F2 =SUM(A2:E2) That formula will &q...

Accessing Client JavaScript after Selecting Row from Grid
Hello, I am currently working with Visual Studio 2008 and asp.net 3.0 I have the following question I hope that someone can help me with: 1) I have a GridView with rows that I read from a database. 2) The user can select a row from the grid, which fires the event: protected void MyGrid_SelectedIndexChanged(object sender, EventArgs e) { <update text field on form> <call client javascript function> } 3) I have the <update text field on form> portion of the routine working, but need to call the <cal...

Sorting Rows by Color
Excel 2003: Is there a way to sort rows by color? Put all the blue rows together, all the green together, etc? There is but you need to work it. Take a look at http://www.xldynamic.com/source/xld.ColourCounter.html#sorting -- HTH RP (remove nothere from the email address if mailing direct) "SharonJo" <anonymous@discussions.microsoft.com> wrote in message news:144901c4f9c9$f734ec80$a601280a@phx.gbl... > Excel 2003: Is there a way to sort rows by color? Put all > the blue rows together, all the green together, etc? ...

EXcluding Zeros from the average in a row
HI I am trying to average a row of numbers (F35:U35) that have numeric zeros in some of the cells. However, I would like to exclude them, and the cells from the calculation "=AVERAGE(F35:U35)". Is there a way to do that? Thanks -- Geo Hi Geo This array formula will do the job: =AVERAGE(IF(F35:U35<>0,F35:U35)) To be entered with <Shift><Ctrl><Enter> instead of <Enter>, also if edited later. -- Best Regards Leo Heuser Followup to newsgroup only please. "Geo" <Geo@discussions.microsoft.com> skrev i en meddelelse news:9C2B0B65-1AF...

Flag row if cell values = something specific
Let's say A1 = top and B1 = Bottom in C1, I want to say that if A1 = top and B1 = bottom then the cell background color of C1 should be red. How can I do this? And I need to do this in a macro. I can't use conditional formating from the menu. I have a recorded macro that does all my formatting and I need to add this. "Some Dude" <sdatt@myplace.com> wrote in message news:e9kw7DrfGHA.4864@TK2MSFTNGP05.phx.gbl... > Let's say A1 = top > and B1 = Bottom > > in C1, I want to say that if A1 = top and B1 = bottom then the cell > background color of...

Count and Reset Button Clicks
Hello Experts. I have a Yahtzee game with 2 buttons/2 macros. One button (Roll Dice) rolls the dice (Calculate) and the second button (Next Player) clears all checkboxes on the sheet. However, I'd like to know if there's a way to disable the first button after it's been clicked 3 times, then re-enable it once the second button is clicked and start the count over... Please advise. I thank you for your time. We can use a Global variable to communicate between the two macros: Dim IAmTheCount As Integer Sub FirstButton() If IsEmpty(IAmTheCount) Then I...

named cells not referenced in other sheets
how do i fix this situation? ...

Row and columns
In excel my rows are numbered 1 through XXX and my columns are also numbered 1 through XXX. On my other computers using the same current version of Excel the columns are identified using Alpha A,B,C,D etc. Anyone know how to change the columns to Alpha v.s. numbers? Joe, tools, optins, general, and uncheck R1C1 reference style -- Paul B Always backup your data before trying something new Using Excel 2000 & 97 Please post any response to the newsgroups so others can benefit from it ** remove news from my email address to reply by email ** "Joe" <anonymous@discussions.mic...

I selected row 1 and row 2 to freeze and it is freezing at row 12
I am selecting row 1 and row 2 to freeze and excel keeps freezing at row 12? Select one row only, if you want 2 rows above where it freezes select row 3, that will leave row1 and 2 unaffected when you scroll down -- Regards, Peo Sjoblom "Lori Brooks" <Lori Brooks@discussions.microsoft.com> wrote in message news:C968CA9B-35A3-427E-993C-D36F3B6D37B7@microsoft.com... >I am selecting row 1 and row 2 to freeze and excel keeps freezing at row >12? Hi, To freeze row 1 & 2 select A3 and then Window|Freeze Panes Mike "Lori Broo...

zeros in cells with formulas
when I enter a formula in a cell it displays a 0, when there is not an answer to the formula. Is there a way to keep the formula but get rid of the zeros any help is greatly apperciated! Thanks, Darrell Two ways, In Tools>Options>View, uncheck the zero values checkbox - that hides them Other way is to trap it, like =IF(formula=0,"",formula) -- HTH Bob Phillips ... looking out across Poole Harbour to the Purbecks (remove nothere from the email address if mailing direct) "Darrell" <anonymous@discussions.microsoft.com> wrote in message news:D4B48DFF-...