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

SUMIF - Alternate Columns

S

srikandi2000

Member
Dear all

I am looking for (non array formula) alternative solution to sumif problem. I edited two solutions from my previous problem and so far only array formula that works. Any idea on what went wrong? I attached the sample workboook

Thanks in advance
 

Attachments

  • example for chandoo_org.xlsx
    9.7 KB · Views: 7
If you are using excel 2010, use the pivot table along with slicers, it will easy & less time consuming than an array formula.
 
Thanks alot Narayan.

Can you explain why it failed to work before?

Btw, I still dont understand why it works

Why I cannot use :
=SUMPRODUCT(($A$2:$A$12=IF($H$1="All";$A$2:$A$12;$H$1))*($B$2:$B$12=IF($H$2="All";$B$2:$B$12;$H$2))*IF($H$3="All";$C$2:$C$12+$D$2:$D$12;IF($H$3="Teacher";$C$2:$C$12;$D$2:$D$12)))

Which produce error result.
 
Srikandi..

I would like to suggest you to go for DSUM, DCOUNT function..

In case of ALL, just delete the cell value..
check here..

Give me some time.. trying to establish ALL for Teacher TeacherType also.. (without Helper)..
 

Attachments

  • example for chandoo_org.xlsx
    9.3 KB · Views: 6
Hi ,

The problem is that the SUMPRODUCT was having the following construct :

=SUMPRODUCT( ( condition 1 ) * ( condition 2 ) , ( columns C & D range ) )

I have changed it to :

=SUMPRODUCT( ( condition 1 ) * ( condition 2 ) * ( columns C & D range ) )

Narayan
 
Narayan: Is it possible to use non adjacent column with your formula? Because that is what I wanted to do.
DebraJ : How do you add Blank to data validation list -- Nervermind... I just use cells for this... Thanks
 
Hi ,

I'll get back to you on the issue of non-contiguous columns ; regarding the other question , if you see the last part of your formula , it is :

IF($H$3="All";$C$2:$C$12+$D$2:$D$12;IF($H$3="Teacher";$C$2:$C$12;$D$2:$D$12))

Here , the first part :

$C$2:$C$12+$D$2:$D$12

is the problem.

The SUMPRODUCT function is not using the arrays in this operation ; it is using only the first elements in C2 and D2.

This also works :

=SUM(IF(($A$2:$A$12=IF($H$1="All";$A$2:$A$12;$H$1))*($B$2:$B$12=IF($H$2="All";$B$2:$B$12;$H$2));IF($H$3="All";($C$2:$C$12+$D$2:$D$12);IF($H$3="Teacher";$C$2:$C$12;$D$2:$D$12))))

entered as array formula , using CTRL SHIFT ENTER.

Narayan
 
DebraJ: Sorry, my internet is having problem. I mean I link to cells such as K1 - K5 which consist all the criteria
Narayan : Thank you for the quick reply. I try to understand it first.
Any idea on non array formula? My original data consist more than 100k record. I tried to avoid array formula.

Btw, I was curious and click on the fx (insert function) button. Surprisingly the formula result on the form was correct but it shows different value on the worksheet
 
One more solution, in E Column add ADD in the heading & sum Number Of Head Teacher+Number Of Teacher.

In your sum product formula

Code: 
=SUMPRODUCT((A2:A12=H1)*(B2:B12=H2),IF(H3="All",E2:E12,IF(H3="Teacher",C2:C12,D2:D12)))

Hope this helps
 
Hi ,

Let me clear one apprehension ; the difference between an array formula and a non-array formula is not likely to be very significant in your case , since you are going to have only one formula ; your dataset may consist of 100000 rows , but the number of formulae used is not going to be that many since you are looking for only one formula which will aggregate the data in those 100000 rows.

The difference in execution time for this one formula may not be that much ; have you tried it out in your real-life working file ?

Narayan
 
You must log in or register to reply here.
Back
Top