CountIfs with multiple Criteria and Two Worksheets

Hey guys, this forum has been really great, and I posted my first question yesterday and they help was phenomenal. Today is part two of this big project I am working on and hopefully I can explain it clearly and get the same great help.

Today's problem has two worksheets "SBDATA" and "MTDATA" -- I will try to be as clear as I can. Both worksheets need the same results (from different columns) combine to a final summary page. These two worksheets cannot be combine for any reason at this time.

The results are appearing on a third worksheet currently labeled "SUMMARY" -- SO, this formula is currently going into cell "B4" on the "SUMMARY" page.

So, here we go!

On the "SBDATA" worksheet Column AH has a data range that starts at cell AH6 and goes down (changing in length each month) There are four potential outcomes in this column -- Leaf, Pear, Orange, and DMC --- I need to only identify the "Pears" in the range ---

IF it is a Pear, I am then moving cell D6 (again a range going down and changing in length each month) -- From here once I know the "Pears" I would like to verify if the "Pears" in this column (D6 and down) have the variable "I" in this colum.

Step 3 -- if it is a "Pear" in column AH and "I" in column D ----> next is to confirm if it is a "1" in column E (again starting from E6 and down)


Finally -- If it is a "Pear" in column AH and "I" in column D and "1 in column E ----> I then need to count all the "1"s in Column Q ---> This range again starts at Q6 and goes down and changing each month. All of these "1" must have a final count in cell "B4" on the "SUMMARY" page...


This is my more complex problems., and I know trying to type it is hard....


Please let me know if you can help !!!!

thanks


-rt

I have attached the spreadsheet....


Although Pear, Orange, and Leaf show up -- DMC could also show up (even though none did at this time)...


I removed all non-used data but left in current columns

thanks

let me know if this makes it clear


rich

Originally Posted by Melvinrobb

It is a little difficult to understand where you want the result displayed in the summary table, and which cells should be used as references, etc...
but... I think this formula does what you want:

Formula:

Please Login or Register  to view this content.

AD5=Pear
AD6=I
AD7=1
AD8=1

change these cell references to wherever these references are in your file. I believe AD5 would be change to B2, but I am not sure where the other criteria is listed. The structure of your files makes it so you will need to manually change the references in each formula.

Does this work?

you lost me.....I am starting with cell C5 on the summary page....This cell needs to calcuate the count of all Pears---> that are "I" in column D --->that also have a "1" in column E --- > that are also giving an Overall result of "1" in column Q on worksheet SBData --- then it has add this total to the same total from MTDATA worksheet (obvisouly MTData) has the same data but in different columns to get one number


make sense ?

that didn't work.....

Here are the totals for box C5 on the summary page it should add 20 from SBData and 23 from MTData that equal 42.

That is 42 Pears that Are I and "1" with a overall score also of 1.

make sense ?

Originally Posted by Melvinrobb

Okay.
The file I attached that was ONLY adding the SB Data was showing 20 in C5, so that portion appears to be correct. The formula that incorporates both is returning 43, so I'm not sure why there is a discrepancy between the number you have, and the number that my formula is coming up with. Assuming you added your manually, I'm guessing you accidently missed one.
I added a formula in column AV of the MTDATA sheet to give a 1 if all of the criteria is met, and it is also coming up with a total of 23 for the MTDATA.

thanks for help...Ill check it as soon as I can...running around right now

Originally Posted by Melvinrobb

Okay.
The file I attached that was ONLY adding the SB Data was showing 20 in C5, so that portion appears to be correct. The formula that incorporates both is returning 43, so I'm not sure why there is a discrepancy between the number you have, and the number that my formula is coming up with. Assuming you added your manually, I'm guessing you accidently missed one.
I added a formula in column AV of the MTDATA sheet to give a 1 if all of the criteria is met, and it is also coming up with a total of 23 for the MTDATA.

I am trying the forumla over and over --- I had to start a new excel file and everytime I copy and paste the forumla in the same cell it returns the value of "0"


can I not copy and paste the forumla ? I don't see any unique Identifiers.....

thanks

Originally Posted by Melvinrobb

Do you have values in AD6 and AD7, similiar to the file I attached?
Simply go through and check and make sure the references in the new file make sense.
For instance, the reference to $B$2 in my formula is referencing "PEAR". Make sure the formula when you copy it is referencing whatever cell "Pear" is in.

Outside of that, you would need to upload the new excel file with the formula copied over for me to know the reason it is not working for you.

DUUHOOOO!

I FORGET THEM....i JUST PUT THEM BACK...IT SEEMS TO WORK...I WILL CALCULATE THE SUMMARY SHEET AND REPORT BACK IF IT WORKS PROPERLY

THANKS

This is solved -- so many thanks

much appreciated

-rt

!!!!thankyyou!!!!!