SUMIF function

[TexasCPA2376]

The SUMIF function returns a value of $132,484. As a financial auditor, I want to verify the accuracy of the function and the resultant value.

Is there a way to determine the cell numbers and the value of that cell. For example: the spread sheet being used has 2866 rows. I want to know which cells that meet the criteria total to the #132,484. I want to know the cell location and the dollar amount in that cell.

Thanks for your help

TexasCPA2376

 

[ETAF]

not sure I fully understand - if you select all the cells - the status bar will give a value
you can also apply a filter and use >0 to filter all the rows that has a value in

 

[vletm]

TexasCPA2376​

Please, send a sample Excel-file to show Your question and expected results.

 

[TexasCPA2376]

not sure I fully understand - if you select all the cells - the status bar will give a value
you can also apply a filter and use >0 to filter all the rows that has a value in

not sure I fully understand - if you select all the cells - the status bar will give a value
you can also apply a filter and use >0 to filter all the rows that has a value in

Thats the problem I don't want to look down all 2866 rows to find out which cells, lets say column C for example, meets the established criteria. (i.e., $132,484)

 

[ETAF]

what if all the cells have $100 in then how will you know what adds up to $132,484
still not understanding
if you use >0 on a filter you will only see the rows that have a value in, what is your audit going to check ????
if you want to check sumif() and see the rows with a value in - cant see what you are trying to do

sumif() will add all the cells in a range .....
so if 20000 cells each have $1 in each it will show 20,000

whats the IF bit
post the formula
and a sample as requested

 

[TexasCPA2376]

DR (Cr) Acct	Dr (CR) Amt
50.05​	-22.63​
50.05​	350.00​
13.01​	1600.00​
50.05​	1112.50​
50.02​	3579.00​
50.01​	222.83​
50.02​	3.95​
50.01​	3.95​
50.02​	37.02​
50.02​	57.31​
50.02​	96.32​
50.03​	103.50​
65.03​	15.00​
50.02​	11.87​

TexasCPA2376​

Please, send a sample Excel-file to show Your question and expected results.

Example the first first column is the criteria range and the second column that is to be added, if it matches the criteria. So I want the total dollar for the criteria of Acct 50.05. I don't to look down all 2000+ rows and find 50.05 and add the related dollar total. Question is there a way to have a listing of all the cells in that column which have the criteria of 50.05?

 

[vletm]

TexasCPA2376​

Please, reread #3 reply.
... You sent something else.

 

[TexasCPA2376]

what if all the cells have $100 in then how will you know what adds up to $132,484
still not understanding
if you use >0 on a filter you will only see the rows that have a value in, what is your audit going to check ????
if you want to check sumif() and see the rows with a value in - cant see what you are trying to do

sumif() will add all the cells in a range .....
so if 20000 cells each have $1 in each it will show 20,000

whats the IF bit
post the formula
and a sample as requeste

As an auditor, I want to prove the SUMIF function is working propery. What if the function failed to meet the criteria of a cell and did not include the value in the grand total or vice-versa- the function picked up a value that did not meet the criteria. In other words, how do I know the function is working like intended?

 

[TexasCPA2376]

TexasCPA2376​

Please, reread #3 reply.
... You sent something else.

 

[ETAF]

if you have a later version of excel then you could use filter()
in a new column with nothing in
maybe

=filter(A2:B2866, A2:A2866=50.05)
will list all the rows with a value of 50.05

1​	249.15​
1​	12​		50.05​	21​
2​	15​		50.05​	33​
	18​		50.05​	51​
50.05​	21​
4​	24​
	27​
	30​
50.05​	33​
89​	36​
	39​
	42​
	45​
1​	48​
50.05​	51​
1​	54​
	57​
	60​
	63​
1​	66​

 

[vletm]

TexasCPA2376​

Okay ... You can see @ETAF's sample Excel-file (sumif-etaf.xlsx) in previous reply.
SUMIF-function works well ... if You'll use it as it should use.

 

[ETAF]

I have updated the sheet

Now you can add an account number into cell D2
and it will output a filter of all the rows into cells E2 to F2
also sums the filtered amount in G2
and uses a SUMIF, in the main table to SUM column B based on the criteria in D2 , using the column A

OR you cna use the filter arrow in A1 to just show a particular account number

Account	Amount		Acc No	Acc No.	Amount	SUM		SUMIF
12.3​	12​		50.05	50.05	21	105		105
2.12​	15​			50.05	33
3.15​	18​			50.05	51
50.05​	21​
12.3​	24​
2.12​	27​
3.15​	30​
50.05​	33​
12.3​	36​
2.12​	39​
12.3​	42​
2.12​	45​
3.15​	48​
50.05​	51​
12.3​	54​
2.12​	57​
3.15​	60​
12.3​	63​
2.12​	66​
3.15​	100​

 

[vletm]

TexasCPA2376​

Your Question is there a way to have a listing of all the cells in that column which have the criteria of 50.05?

You wanted to use snapshots ...

#1 You have something like below. #2 After You've used FILTER-option ...
.
Isn't this one possible way?

I cannot get Your The SUMIF function returns a value of $132,484 -case working?
Where and how did You use it?