Countif questions

[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

 

[Bob Phillips]

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)

 

[Harold]

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

 

[Frank Kabel]

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

 

[Harold]

Thanks guys they worked fine, your legends

Ps happy easte

 

[Chris Rouse]

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

 

[Frank Kabel]

Hi
if you use VLOOKP which creates these errors change these formulas to
=IF(ISNA(VLOOKUP(...)),"",VLOOKUP(...))

 

[Chris Rouse]

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(...)

 

[Chris Rouise]

Sorted it thanks, just needed some coffee

 

[Frank Kabel]

Hi
=IF(ISNA(VLOOKUP(F3,'trainingspreadsheet.xls'!Store_Number,3)),"",VLOOK
UP(F3,'trainingspreadsheet.xls'!Store_Number,3))


--
Regards
Frank Kabel
Frankfurt, Germany

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

name and region into columns G and H from the named range Store_Number
that correspondes to the Number in column F

 

[Chris Rouse]

Just as a side line is it possible to create a button that when clicked on wil display all duplicate for a give range?

 

[Frank Kabel]

Hi
not exactly what you're asking for but see:
http://www.cpearson.com/excel/duplicat.htm#HighlightingDuplicates

--
Regards
Frank Kabel
Frankfurt, Germany

Just as a side line is it possible to create a button that when

clicked on wil display all duplicate for a give range?

 

[chris rouse]

yep, know about that but wanted to transfer that to a button on the tool bar