PDA

View Full Version : MS Excel Issue


allenlikewo
10-11-2004, 10:14 PM
Before I ask I am sorry if this is OT but I dont really post to any other forums and I know there are people smart enough to help me here

Quick question:

This seems simple but I cant figure it out. Does anyone here know how to do a multi-conditional COUNTIF statement just using regular Excel functionality (no VBA or anything).

For example: I have a spreadsheet w/ a few columns in the beginning giving possible types of entries they can be (lest say A, B, and C) and I put an x in the cell of the type that it is

Then in the last column I have a cell where there will either be a Y or N (lest say E).

I can easily use a COUNTIF to see how many entries are in column A, B, or C such as: =countif(A1:A25,x)

or see how many entries have yes or no in the last column such as:=countif(E1:E24,N)

But my problem is what if I only want to see how many Ys I have in column E if there is an x in column A (or B, or C, etc)

Can this be done? If so How?

It seems like an easy thing, I have tried lots of combos of different functions to do but I cant figure it out :banghead:

Thanks in advance for anyone that can help me!

Goon
10-11-2004, 10:19 PM
oi. Excel on Cgtalk...

I use this forum a lot for access related issues:
http://tek-tips.com/index.cfm
not sure if it has an excel forum

Then there's this:
http://www.google.com/search?q=excel+forum&sourceid=firefox&start=0&start=0&ie=utf-8&oe=utf-8

allenlikewo
10-11-2004, 10:24 PM
I was afraid this was the kind of response I would get. But thought it wouldn't hurt to try, plus I think everyone has to use excel for one thing or another.

In the mean time if anyone knows please thow me some knowladge

JamesMK
10-11-2004, 10:56 PM
All I can say is that as soon as you're dealing with multi-conditional stuff like that, it will actually be much, much easier to do it in VBA instead. Do a little google for Excel VBA and I'm sure you'll find something applicable.

allenlikewo
10-11-2004, 11:18 PM
Thanks for responding. Unfortunantley, I can do it in VBA pretty easy. but I am not the only one who will use the Doc and they have to "understand" it and potentially be able to change it. since all you have to do in the new versions of excel is double click a cell with a forumla and it has neet little brackets that can be dragged around and such they could "understand" it pretty easy if I just user regular excel stuff.

Since the rows in any one implementation of the worksheet will not exceed 50 for the time being it is not that big of a deal to do it manually. but: I HATE DOING THING MANUALLY if don't feel like it is neccessarry.

Any way, I'm going home for the day so I will think about it again tomarrow

allenlikewo
10-12-2004, 05:14 PM
YEA!!! I got it! I got it! (and without VBA) :buttrock:

SUMPRODUCT does the trick (pretty simple actually)

here is a simplified version of my formula:
=SUMPRODUCT((I36:I53="N")*(B36:B53="x"))

don't know if anyone cares but it does exactly what I was trying to do, it will give me a count of how many cells =N where it's counter cell in column B is marked with an x. you can do a whole lot with SUMPRODUCT now that I think about it.

CGTalk Moderation
01-19-2006, 01:00 PM
This thread has been automatically closed as it remained inactive for 12 months. If you wish to continue the discussion, please create a new thread in the appropriate forum.