Hi
I have a range of data going accross columns, the entries include, U, T, and F1 to F11.
I have a requirement to count how many of a particular type of entry there are, this needs to be done in groups, e.g, counting attendance bonuses which are included in entries, F5,F6, F8 and F11, (i.e. in one cell i need to total how many F5's,6's,8's & 11's are in that row?!). i just cant quite seem to get the formula right to count them, could anybody please help me out?!
cheers
millsy
Last edited by rjmills18; 10-03-2008 at 07:49 AM.
Hi,
Do you actually need sumproduct? Can't you do what you need with COUNTIF?
Rule 1: Never merge cells
Rule 2: See rule 1
"Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".
i have used a nested countif, sometimes 8 out of the possible 11 'F' choices are in a group though, so i was just trying to tidy it up really
thanks
millsy
If your data range is A2:Z2 try
=SUM(COUNTIF(A2:Z2,{"F5","F6","F8","F11"}))
You can extend that to include any number of values
You could do it with an array..
{=COUNT(IF(A2:A6={"F5","F6","F8","F11"},1,""))}
for example
Or as you'd originally requested, by SUMPRODUCT
=SUMPRODUCT((A2:A6={"F5","F6","F8","F11"})*1)
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks