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

SUMIFS for Excel 2003

M

Martina

New Member
I am trying to write this formula in Excel 2003, which I know has no SUMIFS. I need to find the sum of all items under HQ (column M) that are equal to or less than 100,000 (amounts are in column E).


=SUMIFS(E2:E49,"<=100000",M2:M49,"HQ")


I already tried your solution of ={SUM(IF((...

It would not accept.
 
Hi Martina,


Please see the uploaded file.


https://www.dropbox.com/s/st1u88yor2np3um/Try.xls


Thanks


Sreekhosh
 
Hi Martina,


=SUM(($M$1:$M$100="HQ")*($E$1:$E$100<=100000)*($E$1:$E$100))

(E Col is amount & M col is HQ)


Use Control+Shift+Enter it will work.


Thanks,

Suresh Kumar S
 
Hi Martina,


Suppose you have data in Col E and Col M as follows:


100000>>>> HQ

95000>>>>> HQ

85000>>>>> HQ

120000>>>>> HQ

100003>>>>> HQ

60000>>>>>> HQ


At N1 write:


=SUMPRODUCT(--(M1:M6="HQ"),--(E1:E6<=100000),--(E1:E6))


This will return you 340000 (sum for 100000,95000,85000 and 60000)


Hope this helps.


Kaushik
 
Hi guys. Thank you so much for your replies, but I upgraded to excel 2010 so as to get SUMIFS but I still cannot get the sum. This is my latest query:


How do I get the sum of all entries of less than $100,000 found in column E, that were done in HQ (COLUMN M).


I don't know what to put in the sum range as I only need data from the 2 columns E and M.


SUMIFS(E:E,E2:E47,",=100000",M2:M47,"HQ")
 
Duh and yey! I got it. I think I just needed to put columns in order. Please note the , before the =100000 was correct all along. Cheers guys.
 
You must log in or register to reply here.
Back
Top