COUNTIF (A1:A10=8) but only if the cell next to it is "X"

[juzsp]

I need to report on all the jobs closed in a period but need to separate them into 2 categories.

So in COL (A) I have the period the job was closed and in COL (B) I have the category of the job.

I need a formula that then says something like this;

=COUNTIF (COL_A=8 AND IF the cell next to it COL_B has a value of “CategoryA” then count it)

If only it were as easy as that lol

Thanks for your time and help guys.

[Richard Schollar]

Hi

=SUMPRODUCT(--(A1:A100=8),--(B1:B100="CategoryA"))

Note that you can't use whole column references in Sumproduct (unless on xl2007) - so no A:A or B:B referencing.

For maximum flexibility, you should place the criteria (ie 8 and CategoryA) in separate cells, and reference those cells in the formula.

Richard

[juzsp]

Many thanks for your help richard, works perfectly!

Would the same kind of formula work if say i wanted to sum all the figures for closure times?

Each job has a closure time and i need to get an average for each period!

thanks for help you have saved me a big headache!

-Edit-

ok i gave it ago but didnt work, heres how i did it (don't laugh)

=SUMPRODUCT(--(SUM('veg 06-07'!$N$3:$N$5000))/(COUNTIF('veg 06-07'!$M$3:$M$5000,"=8"))--($Q$3:$Q$5000="Inner"))

[daddylonglegs]

Can you explain what you want to do?

It looks like you want to Average column N when two other conditions are met, Column Q = "Inner" and column M??

[juzsp]

daddylonglegs, you hit the nail on the head. Sorry about my way of explaining things.

Yes, i need to average all the closure times (N) of jobs in the ("Inner" area of london (Q)) that fall in (period "8" (M))

[NBVC]

Please Login or Register  to view this content.

or

Please Login or Register  to view this content.

the last formula must be confirmed with CTRL+SHIFT+ENTER not just ENTER for it to work...you will see { } brackets appear around the formula.

[juzsp]

Thank you SO SO Much! That worked perfectly!