Sumif / count if?

[jamesa2487]

Hi All

I've used a countif formula in the attached for total green , amber and red.

I've highlighted in grey where im stuck, i need it to pull the data based on area number.

Example

Total red = 3

1 in area 2
1 in area 11
1 in area 31

I wanted the boxes in grey to reflect this.

Thanks

[LukeGilfoyle]

This works:

=COUNTIFS($B$2:$B$19,$D23,$H$2:$H$19,F$20)

You had "Automatic Calculations" turned OFF.

Press F9 to re-calculate

Thanks

[leelnich]

JEEZ, That's 20 minutes I'll never get back trying to figure out why COUNTIFS wouldn't work.

...and if you set Column C to all 1's (Region=1), you can check Region AND Area:
=COUNTIFS($H$2:$H$19,F$20,$A$2:$A$19,$C23,$B$2:$B$19,$D23)

Please click the Add Reputation star below any helpful posts, and if you have your answer, mark your thread as SOLVED (Thread Tools up top). Thanks!-Lee

[LukeGilfoyle]

JEEZ, That's 20 minutes I'll never get back trying to figure out why COUNTIFS wouldn't work.

I thought the same until i noticed the formula result was always "2" when starting from F27 and dragging across!

Pressing the little "FX" button next to the forumla bar showed me that the Formula Result on other cells was different to "2"

F9 recalculated and all worked - hope this helped!

[jamesa2487]

Thanks guys!!

Last one - if i want to count amount of "how many fails" what would i use?

Currently set as 3 a a total of three as there have been 3 fails altogether but again to split by area

[LukeGilfoyle]

Try this:

=SUMIFS($G$2:$G$19,$B$2:$B$19,$D23)

EDIT

Use Countifs to count number of fails by area.

[leelnich]

"how many fails" relates to 'Total Fails' (Column J (=10)), correct?
"number with fails" is Column I (=3= RED count)