Using SUMIF with 2 or more criteria

  ElephantRhino 17:47 02 Aug 2006

Is it possible to use SUMIF with more than 1 criteria? I can use it successfully with only 1 criteria but, for example, in a budget management s/sheet, where each customer may have several projects with their own budgets, I want to sum timesheet data, which requires at least 2 criteria - customer and project. It would be even better if I could use 3 and include resource.

  VoG II 18:02 02 Aug 2006

You need to use SUMPRODUCT

Something like

=SUMPRODUCT(--(B2:B7="A"), --(C2:C7="A")), D2:D7)

  ElephantRhino 10:24 05 Aug 2006

Thanks - once I realised you meant the -- within brackets it worked fine. If only I understood why!

  VoG II 12:01 05 Aug 2006

click here for an elegant explanation by Aladin Akyurek.

Basically (B2:B7="A") will return TRUE or FALSE. -- coerces this to produce a number (1 or 0 respectively). -- is the 'cheapest' method of coercion.

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

How secure is your Wi-Fi?

AMD vs Intel: we put the most powerful desktops for designers and artists head-to-head

iPhone tips & tricks

Les meilleurs ordinateurs portables 2017