UDF for sum of multiple criteria in multiple columns and single criteria in multiple colum

Hi Experts,

I am trying to get sum based on multiple criteria from 3 columns and single criteria from 1 columns and got the formula from “Excel Formulas & Functions” forum. However it is not working if criteria mentioned as "*" (means for all records in database).

e.g. Earlier in sumifs function i used "*" as criteria for sum of all the records in database, have created dynamic drop-downs for selection of criteria/options for end user to view the summary of data.

Is there any UDF to get sum based on multiple criteria in multiple columns and single criteria multiple columns......

Please check the attached file for details.

Any help highly appreciated.

Make use of DSUM() function

I have already tried DSUM() function, however not useful for this problem.

any help on above . . . .

Is there any solution for the same ? ? ?

Try something like this...

=DSUM(DATA!A1:F110,"Field 6",SUMMARY!C7:E8)

1) It is not giving correct results.
e.g. Field1 criteria selected as AIR, MAA, PNQ and Field2 criteria selected as LBU, GBU then it is showing as 688, however correct number is 16343.

2) 1st criteria is missing or not included in the formula (Field5 criteria).

Hence it is not workable. . .

Refer the attached file for solution

Hi Sixthsense,

I am very grateful for your help and valuable time.

I am still confused with the solution/formula as results are incorrect. Below is the difference between correct results and formula results.

Criteria for Field 1 - AIR, MAA, PNQ
Criteria for Field 2 - LBU, GBU
Criteria for Field 3 - "*"

Elements- Formula results- Correct results
Communication Cost- 1951- 1884
Company Cars- 945- 978
Employee Benefits- 882- 1033
Food & Canteen- 1932- 1888
Housing Expense- 870- 922
Local Data Communication- 845- 845
Salaries and Related- 1153- 2147
Staff Training- 1009- 1882
Recruitment Cost- 1514- 1379
Travel- 2100- 1333
Sales Cost- 287- 580
Support functional cost- 1342- 504
Taxes- 437- 968

Could you please look into the same . . .

Please let me know the drop down selection conditions for it…

have edited earlier post

Criteria for Field 1 - AIR, MAA, PNQ = IN
Criteria for Field 2 - LBU, GBU = BU
Criteria for Field 3 - "*" = All

This is the result I am getting for the said criteria’s

	C	D	E
7	Field 1	Field 2	Field 3
8	AIR	LBU	PNQ
9	MAA	GBU
10	PNQ

	C	D
12	Elements	Cost
13	Communication Cost	1911
14	Company Cars	726
15	Employee Benefits	626
16	Food & Canteen	1932
17	Housing Expense	870
18	Local Data Communication	845
19	Salaries and Related	1153
20	Staff Training	943
21	Recruitment Cost	1509
22	Travel	1998
23	Sales Cost	287
24	Support functional cost	1342
25	Taxes	437

There is no "PNQ" in Field 3, it should be "*" (for all records).
And if it is PNQ then results should be 0 for all because it is not exist in the data.

Here is my complete workout for your reference. Please check and tell where it is failing…

	A	B	C
1	Elements	Amount	Group
2	Communication Cost	40	Air/LBU
3	Company Cars	219	Air/LBU
4	Employee Benefits	256	Air/LBU
5	Staff Training	66	Air/LBU
6	Recruitment Cost	5	Air/LBU
7	Travel	102	Air/LBU
8	Employee Benefits	120	MAA/GBU
9	Food & Canteen	997	MAA/GBU
10	Staff Training	780	MAA/GBU
11	Recruitment Cost	610	MAA/GBU
12	Travel	792	MAA/GBU
13	Communication Cost	1911	PNQ
14	Company Cars	726	PNQ
15	Employee Benefits	506	PNQ
16	Food & Canteen	935	PNQ
17	Housing Expense	870	PNQ
18	Local Data Communication	845	PNQ
19	Salaries and Related	1153	PNQ
20	Staff Training	163	PNQ
21	Recruitment Cost	899	PNQ
22	Travel	1206	PNQ
23	Sales Cost	287	PNQ
24	Support functional cost	1342	PNQ
25	Taxes	437	PNQ

	A	B
1	Elements	Total
2	Communication Cost	1951
3	Company Cars	945
4	Employee Benefits	882
5	Food & Canteen	1932
6	Housing Expense	870
7	Local Data Communication	845
8	Salaries and Related	1153
9	Staff Training	1009
10	Recruitment Cost	1514
11	Travel	2100
12	Sales Cost	287
13	Support functional cost	1342
14	Taxes	437

	C	D	E
7	Field 1	Field 2	Field 3
8	AIR	lbu	*
9	MAA	gbu
10	PNQ

	C	D
12	Elements	Cost
13	Communication Cost	1951
14	Company Cars	945
15	Employee Benefits	882
16	Food & Canteen	1932
17	Housing Expense	870
18	Local Data Communication	845
19	Salaries and Related	1153
20	Staff Training	1009
21	Recruitment Cost	1514
22	Travel	2100
23	Sales Cost	287
24	Support functional cost	1342
25	Taxes	437

I am looking for sum based on multiple criteria.

e.g. If we select below criteria
Field 1 -AIR, PNQ, MAA
Field 2 - LBU, GBU
Field 3 - "*" (all records)
then results should be same as attached file. (please refer attached file, I have filtered data as per selected/mentioned criteria for better understanding)

However by using DSUM() function results are totally incorrect like Communication Cost is 1884 not 1951.

I am looking for sum based on multiple criteria.

e.g. If we selected below criteria
Field 1 -AIR, PNQ, MAA
Field 2 - LBU, GBU
Field 3 - "*" (all records)
then results should be same as attached file. (please refer attached file, I have filtered data as per selected/mentioned criteria for better understanding)

However by using DSUM() function results are totally incorrect like Communication Cost is 1884 not 1951.

Hi All,

I have created below function for this, however only one Criteria option added and wanna add 4 more criteria option in the same.

Please Login or Register  to view this content.

Any help highly appreciated

Thanks

Based on you Post # 16 File

In K9 Cell

=SUMIFS(F:F,E:E,J9,A:A,$J$4,B:B,IF(OR($K$4={0,""}),"*",$K$4),C:C,IF(OR($L$4={0,""}),"*",$L$4))+SUMIFS(F:F,E:E,J9,A:A,$J$5,B:B,IF(OR($K$5={0,""}),"*",$K$5),C:C,IF(OR($L$5={0,""}),"*",$L$5))+SUMIFS(F:F,E:E,J9,A:A,$J$6,B:B,IF(OR($K$6={0,""}),"*",$K$6),C:C,IF(OR($L$6={0,""}),"*",$L$6))

Drag it down...

Originally Posted by :) Sixthsense :)

Based on you Post # 16 File

In K9 Cell

=SUMIFS(F:F,E:E,J9,A:A,$J$4,B:B,IF(OR($K$4={0,""}),"*",$K$4),C:C,IF(OR($L$4={0,""}),"*",$L$4))+SUMIFS(F:F,E:E,J9,A:A,$J$5,B:B,IF(OR($K$5={0,""}),"*",$K$5),C:C,IF(OR($L$5={0,""}),"*",$L$5))+SUMIFS(F:F,E:E,J9,A:A,$J$6,B:B,IF(OR($K$6={0,""}),"*",$K$6),C:C,IF(OR($L$6={0,""}),"*",$L$6))

Drag it down...

Not Working . . .

Sorry boss I tried my level best to help you...

I hope someone will take care of your query in a better way as you expect...

Best of Luck!

Thanks Bro . . .

If field 3 is "*" (all records) then why use any search on it at all? (field 3 apparently does not matter, and therefore can be discarded from searching results?)

Edit-
IF I am mis-reading your criteria, I am sorry, but that seems to be what I am reading, and if so, then that is 1 less search criteria, which makes the formulas/functions easier, IMO

Edit 2
missed the VBA, now you need 5 criteria?

Hi,

I have to say that I think your set-up in this sheet leaves a lot to be desired. However, in D13 and copy down, array formula:

=SUM(IF(ISNUMBER(MATCH("~*",$C$8:$C$10,0)),1,(ISNUMBER(MATCH(DATA!$A$2:$A$110,$C$8:$C$10,0))))*IF(ISNUMBER(MATCH("~*",$D$8:$D$10,0)),1,(ISNUMBER(MATCH(DATA!$B$2:$B$110,$D$8:$D$10,0))))*IF(ISNUMBER(MATCH("~*",$E$8:$E$10,0)),1,(ISNUMBER(MATCH(DATA!$C$2:$C$110,$E$8:$E$10,0))))*(DATA!$E$2:$E$110=C13)*DATA!$F$2:$F$110)

Regards

I could be way off, but if you have a * representing "include all" then there is no need to use that criteria for your sum, so you could replace bits in your formula like:
(ISNUMBER(MATCH(DATA!$C$2:$C$110,$E$8:$E$10,0)))
with
if(or(E8="*",E9="*", E10="*"),1,(ISNUMBER(MATCH(DATA!$C$2:$C$110,$E$8:$E$10,0))))

I think the whole formula would become (array formula):
=SUMPRODUCT(IF(OR(C$8="*",C$9="*",C$10="*"),1,(ISNUMBER(MATCH(DATA!$A$2:$A$110,$C$8:$C$10,0))))*IF(OR(D$8="*",D$9="*",D$10="*"),1,(ISNUMBER(MATCH(DATA!$B$2:$B$110,$D$8:$D$10,0))))*IF(OR(E$8="*",E$9="*", E$10="*"),1,(ISNUMBER(MATCH(DATA!$C$2:$C$110,$E$8:$E$10,0))))*(DATA!$E$2:$E$110=C13)*DATA!$F$2:$F$110)
in D13 and drag down.

This gives me the following results:
980
701
256
0
120
845
1387
939
5
102
0
504
968

Note - this is in reference to your first attachment

@yudlugar

Isn't that just precisely what I've done, though with a MATCH construction instead of your OR()?

Regards

Ah! Well, there's more than one way to skin a cat!

Edit: Actually, just a thought, but notice you didn't add a tilde ~ before your asterisk - and you get different results to me - I believe this is necessary in this case?

Regards

Edit: apologies, it doesn't appear to be required in this case.

Your results are for field 2 without GBU included, and seem perfectly good.

Regards

Yes, the use of the asterisk as a wildcard doesn't seem to apply when used like this in an IF statement. Just a thought...

And we must stop crossing!

Assuming that any * would go in the first criteria row only (row 8) then you can use this non array formula

=SUMPRODUCT((ISNUMBER(MATCH(DATA!A$2:A$110,C$8:C$10,0))+(C$8="*")>0)*(ISNUMBER(MATCH(DATA!B$2:B$110,D$8:D$10,0))+(D$8="*")>0)*(ISNUMBER(MATCH(DATA!C$2:C$110,E$8:E$10,0))+(E$8="*")>0)*(DATA!E$2:E$110=C13),DATA!F$2:F$110)

Hi All,

Thanks a ton for your help on this.

@ daddylonglegs – exactly, I was looking for the same