Need a formula for sumifs

[Shruti]

Hi,

Could you please help me with a formula that would best suit if i have to do a sum of rows where apples or oranges are available in the column B & C. But the row should be summed only once. Basically the output of below should be 85 using a formula

1 Apples oranges 5
2 apples bananas 10
3 oranges apples 15
4 apples apples 20
5 bananas oranges 30
6 grapes oranges 5
7 bananas grapes 10
8 grapes grapes 15

I am able to do for 1 column but joining the 2 columns the values are coming incorrect.
=SUMIFS(D1:D8,B1:B8,{"apples","oranges"})
results in 35 which is correct

=SUM(IF(D1:D8,B1:B8,{"apples","oranges"})+IF(D1:D8,C1:C8,{"apples","oranges"}))
returns an Value error

 

[Somendra Misra]

Hi Shruti,

One alternative:

=SUMPRODUCT(((COUNTIF($F$1:$F$2,$B$1:$B$8)+COUNTIF($F$1:$F$2,$C$1:$C$8))>0)*($D$1:$D$8))

With apples & oranges in F1 & F2.

Regards,

 

[Shruti]

Thanks for the response but does blanks have an impact on the calculations..

I have about 700 rows of data and and 7 criterion to search on with some of the rows having blank data in B, C or D.

this formula works perfectly for the example set but my actual data it is failing.

 

[Somendra Misra]

@Shruti

It's always better to give a sample file with the type of data that you are going to have,with all the variations.

Regards,

 

[Shruti]

Hi,

Sorry for not sharing the details. I can't really share the exact data as it is confidential but i have tried to prepare a sample from it.

 

[Somendra Misra]

Hi,

Sorry for not sharing the details. I can't really share the exact data as it is confidential but i have tried to prepare a sample from it.

No need to upload your original file, we always require a sample file having same structure.

See the file, only increase and change the ranges.

Regards,