Sumif query

[ArcticWolf]

Hi,

I have a sheet of sales data (c1000 lines) and need to sum it all up into
areas.

My sales data is:
Area,Value,Include
1,10,Yes
2,9,Yes
3,8,No
4,7,No
5,6,Yes
1,5,Yes
2,5,Yes
3,2,No
4,10,No
5,6,Yes

My results table needs to show the following:

Area,Value
1,15
2,14
3,0
4,0
5,12

Basically I need to sum the value for each area 'only' if the Include column
= yes.

I've tried looking at the info on xldynamic.com but can't seem to get the
right formula together, not sure if I need a sumif or maybe a sumproduct?

Any help would be appreciated.

Thanks in advance.

AW

 

[Eduardo]

Hi,
I assume that the results table start in column D1 to D5 so in D1 enter

=sumproduct(--(D1=$A$1:$A$100),$B$1:$B$100)

then copy the formula down to D5

I this was helpful please click yes, thanks

 

[Francis]

try this
=SUMPRODUCT(--($A$2:$A$10=E2),--($C$2:$C$08="yes"),$B$2:$B$10)
and copy down. E2 is the cell you type the area number
--
Hope this is helpful

Pls click the Yes button below if this post provide answer you have asked

Thank You

cheers, francis

Am not a greek but an ordinary user trying to assist another

 

[Jacob Skaria]

Refer the Query Area cell and try

=SUMPRODUCT(--(A:A=<ReferAreaCell>),--(C:C="Yes"),--(B:B))

If this post helps click Yes

 

[ArcticWolf]

Thanks guys, I've got it working now. Thanks also for the quick responses,
much appreciated.

ATB,

AW