using COUNTIF (or something else) to count with conditions

[aaron85]

I want to use count if to return a number with a condition. eg =COUNTIF(B2:D4, 10) i want to know how many times 10 appears within this range, which is quite simple. but i also want to know how many times 10 appears when 4 is in the same row. i have had a look around and i haven't been able to see how to do it with countif and i have seen SUMIF but it does not seem to give me the result i want. and i have looked at pivot tables but i am not sure whether or not this will get me my answer either? can anyone point me in the right direction for writing a formula so that i can count how many times 10 appears with 4 in the same row?

I want to eventually be able to do this on a larger scale with more conditions but i am hoping if someone can get me started with one condition i will be able to replicate it on a larger scale.

thanks

[NBVC]

In what row will the 4 appear? or do you mean column?

[DonkeyOte]

If I've understood, one way:

=SUMPRODUCT(--(COUNTIF(OFFSET(B2:D2,ROW(B2:D4)-ROW(B2:D2),0),4)>0),--(COUNTIF(OFFSET(B2:D2,ROW(B2:D4)-ROW(B2:D2),0),10)>0))

but this is Volatile so with larger ranges and/or used in high volume this would be a poor performer.

[aaron85]

In what row will the 4 appear? or do you mean column?

i have put a screenshot in to explain what i mean.

thanks

i am also going to try DonkeyOte's suggestion

[JBeaucaire]

Maybe simply:

=SUMPRODUCT(--($B$2:$B$4=10),--($E$2:$E$4=3))

...expand the row count as needed.

[aaron85]

both gave me the solution i needed, thanks
=SUMPRODUCT(--($B$2:$B$4=10),--($E$2:$E$4=3)) was probably more effective for what i wanted as it counted how many times the combination happened. is there a way to extend this (or maybe a seperate function) to apply conditional formatting to the cells eg all cells with 10 have background of red? I know this can be done in conditional formatting but can't seem to get it to have more than 3 conditions using the wizard.

thanks

[JBeaucaire]

Conditional formatting is limited to 3 colors in Excel 2003.

If the current question has been successfully resolved, be sure to EDIT your original post, click Go Advanced and mark the PREFIX box [SOLVED].

(Also, use the blue "scales" icon in our posts to leave Reputation Feedback, it is appreciated)

Then start a new thread with your new topic(s) as needed.

[DonkeyOte]

both gave me the solution i needed, thanks
=SUMPRODUCT(--($B$2:$B$4=10),--($E$2:$E$4=3)) was probably more effective for what i wanted as it counted how many times the combination happened. is there a way to extend this (or maybe a seperate function) to apply conditional formatting to the cells eg all cells with 10 have background of red? I know this can be done in conditional formatting but can't seem to get it to have more than 3 conditions using the wizard.

thanks

Yes, the formula provided in post #3 was based on the premise that both the 10 & 4 were to appear together within the same range of data (ie cols B:D) on the same row and that either/or could technically appear multiple times, eg:

4,10,10

to count as 1... these requirements were what led me to the volatile Sumproduct (OFFSET) which if possible is best avoided.

If JB's alternative approach works for you that's great though I confess I am still slightly curious as to why you referenced B2:D4 in your initial COUNTIF if only B2:B4 was of interest ?
(note if you do need to use B:D then you may need to revise the approach if 10 could appear more than once in any given row of data so as not to over count)

[JBeaucaire]

That's true. If the 10 really could be columns B, C or D, then you would need this older version of the SUMPRODUCT() approach:

=SUMPRODUCT(($B$2:$D$4=10)*($E$2:$E$4=4))

..although this might also give odd results if more than 1 column had 10 in the same row.

[DonkeyOte]

As illustrated in earlier iteration:

=SUMPRODUCT((COUNTIF(OFFSET(B2:D4,ROW(B2:D4)-ROW(B2:D2),0),10)>0)*(E2:E4=4))

would give you the correct result in case of duplicity being a potential issue but obviously Volatile Array... it all boils down to the requirements