Jump to content

Welcome to Geeks to Go - Register now for FREE

Geeks To Go is a helpful hub, where thousands of volunteer geeks quickly serve friendly answers and support. Check out the forums and get free advice from the experts. Register now to gain access to all of our features, it's FREE and only takes one minute. Once registered and logged in, you will be able to create topics, post replies to existing threads, give reputation to your fellow members, get your own private messenger, post status updates, manage your profile and so much more.

Create Account How it Works
Photo

Need Help with Formula in Excel


  • Please log in to reply

#1
KINNEY1978

KINNEY1978

    Member

  • Member
  • PipPip
  • 62 posts
Hello,

I am trying to write a formula in Excel to do the following:

Example:

Say I have a spreadsheet with 4,000 lines.

I need to gather counts based on certain/multiple criteria.

I need to count if:


B2 = FTIC

E2 = STU

F2 = 1 AND/OR G2 = WH

AN2 = 2011FA1

I just can't seem to figure this one out. I've tried both SUMPRODUCT AND COUNTIF but just can't seem to get it.

If someone can assist based on the example, I would GREATLY appreciate it.

As usual, thank you......this forum rocks!
  • 0

Advertisements


#2
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
As ever, several ways to achieve things in Excel. Why not try a simple IF statement?

=IF(B2="FTIC",IF(E2="STU",IF(OR(F2=1,G2="WH"),IF(AN2="2011FA1",1,0),0),0),0)
  • 0

#3
KINNEY1978

KINNEY1978

    Member

  • Topic Starter
  • Member
  • PipPip
  • 62 posts
Thank you, BUT it's returning 0 results, which I have verified to be incorrect in the data.

Any other suggestions?
  • 0

#4
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
Where are you entering the statement? What is actually in B2, E2, F2, G2, and AN2?

Is there a row where Bn="FTIC", En=STU, Fn=1, Gn=WH, and ANn=2011FA1?
  • 0

#5
KINNEY1978

KINNEY1978

    Member

  • Topic Starter
  • Member
  • PipPip
  • 62 posts
Absolutely. I've pulled a sample of at least 308 records from the file that meet the criteria. Had a sample to post but it says that I'm not permitted to post this kind of file???

For all 308 lines,

B= "FTIC"

E= "STU"

F= "1" AND/OR G="WH"

AN= "FTIC"

The formula that you gave me was entered into a separate dashboard sheet titled "Paste Demo Report Here" (Specifically in cell B8). And YES, I did account for the sheet reference.

This is what the formula looks like in my workbook:

=IF('Paste Demo Report Here'!B:B="FTIC",IF('Paste Demo Report Here'!E:E="STU",IF(OR('Paste Demo Report Here'!F:F=1,'Paste Demo Report Here'!G:G="WH"),IF('Paste Demo Report Here'!AN:AN="2011FA1",1,0),0),0),0)

I'd appreciate any assistance that you can provide.

Kind regards,

Edited by KINNEY1978, 27 August 2011 - 08:22 AM.

  • 0

#6
Vino Rosso

Vino Rosso

    Visiting Staff

  • Visiting Consultant
  • 235 posts
Ah, OK so what you want is something like:

=COUNTIFS(B:B,"FTIC",E:E,"STU",F:F,1,G:G,"WH",AN:AN,"FTIC")

But the above caters for Fn=1 AND Gn="WH" but doesn't cater for Fn=1 OR Gn="WH". I don't think OR statements can be used in COUNTIFS. I'll need to think.
  • 0

#7
KINNEY1978

KINNEY1978

    Member

  • Topic Starter
  • Member
  • PipPip
  • 62 posts
Ok, thanks.

I'll wait and hopefully there will be a solution.

I need the AND/OR to be included in the formula in order to populate the results needed. (F= "1" AND/OR G="WH")

I REALLY appreciate your help.

Kind Regards,

Edited by KINNEY1978, 28 August 2011 - 07:00 AM.

  • 0






Similar Topics

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users

As Featured On:

Microsoft Yahoo BBC MSN PC Magazine Washington Post HP