SOLVED

help me with sumifs

Occasional Contributor

hi, can anyone plz help me. i've wanted to do =sumifs(). to calculate the total income from my costumers. (im practicing). after i've finished writing. the first box has no value in it. instead, there is a ( - )

inside of it. the others are working fine. just the first one doesn't

 

6 Replies

@mohaws 

 

First of all, that (-) could very well BE a value. Specifically, zero, which (depending on format settings) will sometimes appear as "-"     So the first question to you, "Is it at all possible that the correct sum for that particular customer is zero?"

 

If not, is it possible for you to post a copy of the spreadsheet (removing real customer names) on OneDrive or GoogleDrive, and then pasting a link here that grants access so we can see it?

thanks for the answer. the value isn't =0 , and i've just discovered that sumifs is not taking all of the rows. ex: i have a customer who had bought 2 things (2 rows in excel) but sumifs is taking just the first row. i'll try and send you a copy
https://1drv.ms/x/s!AvWgMrKZGLsrliVuQYpRz9g3vqbH?e=DrdCe4

here is the link. i've tried one more time to solve it myself. now it shows the calculated sum for the first name. and when i copy it. it copies the same numer
best response confirmed by mathetes (Respected Contributor)
Solution

@mohaws 

 

You shared the file as "Read only" so I'm not actually able to edit it. I think the mistake is in the first criterion, where you specify the range as $K$9:$K$30 and the criterion as $K$9...try changing that latter to D12 and copying the formula down column E.

mathetes_0-1670517863043.png

 

The revised formula then would read

=SUMIFS($P$9:$P$30,$K$9:$K$30,D12,$Q$9:$Q$30,"payment") 

 

when you copy that down the D12 changes to E12, F12, and so on

 

oh. sorry i forgot to make it costumizable for all. so i've copied the formula and paste it. It worked but still one of the boxes have a zero. i will solve the problem and make the file writeable
thank you very much. i found it. in my original sheet i've made a mistake. the costumer had payed 2 times but i wrote it "repaied" thats why the box was = zero. after correcting my mistake excel displayed everything. thanks alot