Excel Issue ( Complex sum )

thecroghanlad

Hi

Appreciate some help out there if anyone can oblige

I have the below worksheet with data that I need to manipulate to go into another sheet


AREA 1 SYSTEM 1 CORE EVENING WEEKEND
N Y 7 0 0
Y N 15 0 0
Y Y 51 0 0
Y Y 45 0 0


I want to get a sum of the core figures if AREA 1 and SYSTEM 1 are both "Y"
I have got a count of the event using the following formula

=SUMPRODUCT(--(data!A2:A5="Y"),--(data!B2:B5="Y"))

Core Evening Weekend
Area 1 2
SYSTEM 1 2


So what I would be expecting is that core would have a total of 96 and so on and on

Any thoughts ???

Mort5000

Option 1:
Use 1 and 0 as your Y and No indicators and then just multiply all the columns, area x system x core

Option 2:
Use nested IF functions to conditionally get your values.
=IF(B15="y",IF(C15="y",D15,0),0)

SalteeDog

You can use the SUMIFS function - which is specifically for this purpose.

i.e =SUMIFS(C2:C5,A2:A5,"Y",B2:B5,"Y") will do what you need.


SUMIFS, SUMIF and indeed COUNTIF are very handy functions.

thecroghanlad

Thanks folks - I was over complicating things - both solutions are exactly what I needed - Regards