Countif questions

  • Thread starter Thread starter Harold
  • Start date Start date
H

Harold

Hi all,
I am trying to write a spreadsheet but am stuck for two lines
how do you write this correctly in excell
1. countif(a2:a5,"=1").and.countif(b2:b7,"=7")
2. countif (a2:a5,"between 1 and 2")

Thanks for your hel
 
countif(a2:a5,1)+countif(b2:b7,7)

=SUMPRODUCT((A2:A5>=1)*(A2:A5<=2))


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
 
Hi
Thanks for your help, the sumproduct was perfect but (sorry look)
wasn't very clear in the first part of the question. what I wa
trying to get is;

to get a count of the number of times that both item 1 and item 2 matc
the criteria

E.G. item 1 doesn't match item 2 does ....not counted
Item 2 doesn't match item 1 does ....not counted
Item 1 and item 2 match ...counted


Thank you again for the assistance

ps. is there a book you can reccomend that would be helpful, I hire
one from the library but it was incredibly basic even for someone wh
knows as little as I d
 
Hi
for the first one use
=SUMPRODUCT((A2:A5=1)*(B2:B5=7))
or
=SUMPRODUCT(--(A2:A5=1),--(B2:B5=7))


Note that the ranges have to be equal
 
I have been looking at this problem and I tried this but I have #n/a in my columns as they are via a lookup table
The SUMPRODUCT does not like this and will only return a correct value if there is data in the cell. Is there any way to get around this problem

Chris
 
Hi
if you use VLOOKP which creates these errors change these formulas to
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))
 
being thick her
My forumla is =VLOOKUP(F3,'trainingspreadsheet.xls'!Store_Number,3
So it checks the contents of F3 (which is a number) then inserts the name and region into columns G and H from the named range Store_Number that correspondes to the Number in column

So do I replace the ... with what in your formul
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...)
 
Hi
=IF(ISNA(VLOOKUP(F3,'trainingspreadsheet.xls'!Store_Number,3)),"",VLOOK
UP(F3,'trainingspreadsheet.xls'!Store_Number,3))


--
Regards
Frank Kabel
Frankfurt, Germany

Chris Rouse said:
being thick here
My forumla is =VLOOKUP(F3,'trainingspreadsheet.xls'!Store_Number,3)
So it checks the contents of F3 (which is a number) then inserts the
Click to expand...
name and region into columns G and H from the named range Store_Number
that correspondes to the Number in column F
 
Just as a side line is it possible to create a button that when clicked on wil display all duplicate for a give range?
 
Back
Top