• 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.

Problem in sumifs

Sir,

I am having problem in sumifs formula even if i am using the correct formula but some problem is there. please help i am attaching a file for your reference.

Please let me know where i am wrong

Thanks
Manish
 

Attachments

  • Book1.xlsx
    13.8 KB · Views: 1
Thanks Jaya,

But it is not working.

This formula i used earlier but the result is different. if we use pivot table we can see the sum or with the filters.
=SUMPRODUCT(($A:$A=$I$3)*($B:$B=$J$3)*($C:$C=$K$3))


Aside to Hui sir and Narayan Sir please help
 
Narayan Sir,

this will work for particular "=" but i have different age groups like
>6-7
>2-3
>3-4
>1-2
>7-8
>8-9
>5-6
>9-10
>10-15
>15-20
<=1
>4-5


and Criteria
<=150
<=75
<=350

How it will define "-" among them
 
Hi Manish ,

The "=" is used to signify that what is in J3 and K3 is to be used a text , and not as criteria.

You are having the data >4-5 as a text string in your column B ; within the SUMIFS , if you do not use the construct "=" & J3 , then using only J3 makes the SUMIFS take >4-5 as the criterion , which means your data should be having only the text string 4-5 and the criterion becomes greater than 4-5.

Using the construct "=" & J3 makes the SUMIFS take the data in J3 as a text string and the criterion as equality. What ever text you enter in J3 will be treated as text , and the criterion will always be equality.

Try entering all the different values you have posted in J3 and K3 and see for yourself. Thus , entering >6-7 in J3 and <=75 in K3 will give you the result 80.

Narayan
 
Back
Top