• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Summing Unique Values With Multiple Criteria

Kenshin

Member
Hello friend sorry to bother you again but I'm confused about this problem, appreciate if you have a chance to take a look at my file

thank you ninjas
 

Attachments

  • My Question.xlsx
    12.5 KB · Views: 4
I2:
=SUMIFS($D$3:$D$68,$A$3:$A$68,">="&H4,$A$3:$A$68,"<="&H5,$E$3:$E$68,H2)
or
=SUMPRODUCT(($D$3:$D$68)*($A$3:$A$68>=H4)*($A$3:$A$68<=H5)*($E$3:$E$68=H2))

Copy either to I8 & I14

I am not sure what the criteria "Apel" refers to?
 
Sorry I'm fix that mistake, sorry Hui maybe I'm not clear at first, I want summing that exclude the same shop with criteria at the file

For example Mr. Zaenal bought Banana that has shop named "Light" 3 times, i need remove it the duplicates so i just need 1 shop to sum
 

Attachments

  • My Question.xlsx
    12.5 KB · Views: 4
I'm more confused now

"Mr. Zaenal bought Banana that has shop named "Light" 3 times, i need remove it the duplicates so i just need 1 shop to sum"


But in your data Mr Zaenal didn't buy any Banana's ???

can you please give us an example of what this:
upload_2017-8-11_15-59-6.png
should add up to
maybe highlight the items that make it up
 
Sorry I mean Apple so i have four different criteria
1. Name
2. Fruit
3. Date
4. Date

For the date criterion is an apple purchase by Mr. Zaenal from 1/7/2017 to 7/7/17
 
I2:
=SUMIFS($D$3:$D$68,$A$3:$A$68,">="&H4,$A$3:$A$68,"<="&H5,$E$3:$E$68,H2,$C$3:$C$68,H3)
or
=SUMPRODUCT(($D$3:$D$68)*($A$3:$A$68>=H4)*($A$3:$A$68<=H5)*($E$3:$E$68=H2)*($C$3:$C$68=H3))

copy down to I8 & I14
 
Sorry Hui but I need exclude duplicates on "Shop" with criteria above, i don't think SUMIFS dunction can do that
 
Hi ,

Your requirement is still not clear ; can you manually indicate what the results should be for the 3 cases that you have given in your workbook ? Once we know what the formula is supposed to return , it will be easier to suggest the correct formula.

Narayan
 
Do you mean like this?
upload_2017-8-11_19-5-49.png

see attached file:
 

Attachments

  • My Question-1.xlsx
    13.4 KB · Views: 3
Try...........

In I2, formula copy down :

=IF(G2="","",SUMPRODUCT((E$3:E$68=H2)*(C$3:C$68=H3)*($A$3:$A$68>=H4)*($A$3:$A$68<=H5)*(MATCH($A$3:$A$68&$E$3:$E$68&$C$3:$C$68&B$3:B$68,$A$3:$A$68&$E$3:$E$68&$C$3:$C$68&B$3:B$68,0)=ROW(B$3:B$68)-ROW(B$3)+1)*D$3:D$68))

Regards
Bosco
 

Attachments

  • Sum unique values.xlsx
    13.6 KB · Views: 7
Back
Top