sumifs criteria not working

[DEEARO]

Hi all,


thank you in advance

i have created a formula with few criteria's, but when i add one extra criteria's it stops working.


this formula not working
=SUMIFS(TABLE3[TotalNo.Vacancies],TABLE3[Week Commencing],D7,TABLE3[JCPLocation],D6,TABLE3[TypeofEvent],"Employer")

this formula works but i need the extra criteria's which the above formula has
=SUMIFS(TABLE3[TotalNo.Vacancies],TABLE3[JCPLocation],D6,TABLE3[TypeofEvent],"Employer")

[AliGW]

You'll need SUMPRODUCT. The dates in B are not whole numbers, so try this:

=SUMPRODUCT(TABLE3[TotalNo.Vacancies]*(INT(TABLE3[Week Commencing])=D7)*(TABLE3[JCPLocation]=D6)*(TABLE3[TypeofEvent]="Employer"))

[JohnTopley]

"Date of Event" is Date+Time so change to Date-only

[AliGW]

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.

[AliGW]

No reply, not marked as solved ... Please don't fail to acknowledge the help you have been given here. "Thanks in advance" in your opening post is not really a substitute.

[DEEARO]

Hey, thank you everyone for your help

i just tried your formula on sheet week3 and it worked. Thank you ... .However i am still struggling.

some place the sumifs works, but some place dont-conufsing. Sumifs is working on sheet WEEK2.


please see sheet AVERAGE: i am sumifs formula and getting results, however any results with the date 31/05/2024 its not being added up by the formula. the total should be 68, getting 65. I am happy to change the formula New Events Campaign - in column A and B. I changed the formula in column b, to INT(d2) and everything else stopped working.

[AliGW]

Where is the formula you want me to look at? Give me a cell reference.

I am not sure that you have understood - you will not be able to use SUMIFS or AVERAGEIFS with your data because of the limitation I pointed out in post #2.

[DEEARO]

on the sheet Average i am getting results buts just not totalling correctly. It is not adding results for 31/05/2024. total should me 68, and i have got 65.

Cell C10 to C26

[AliGW]

I can't find one location that has an entry for 31/05/2024 in that table (TABLE3).

[DEEARO]

table 3 =

b18
b77
b108
b116
b127

[AliGW]

So why didn't you highlight these for me? YOu are expecting me to go hunting for things when you know where they are! I'll have another look, but try to help me here, please!

[AliGW]

It's the same issue as I mentioned in post #2, so you are NOT understanding the issue.

Those dates are NOT whole numbers:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	B
1	Date
18	45443.9583
77	45443.9583
95	45443.9583

Sheet: New Events Campaign

So unless you change them, you cannot use SUMIFS.

[DEEARO]

i am happy to change the formula in "table 3" column "Date" to make it an whole number. the reason i am asking, as i have used sumifs everywhere , so going to take me time to change it.

[AliGW]

So change the dates to whole numbers, then - your SUMIFS will then work.

Otherwise, use this:

=SUMPRODUCT((INT(TABLE3[Date])>=$F$5)*(INT(TABLE3[Date])<=$F$6)*(TABLE3[JCPLocation]=$B10)*(TABLE3[TypeofEvent]="Employer")*TABLE3[No.Employers/Provide])

[JohnTopley]

The issue your comparing DATE+TIME rather than DATE only:

C5 = "20/05/2024 23:00:00"

not "20/05/2024" as I would expect

"Week Commencing" likewise are DATE+TIME

[AliGW]

Date formula in B2:

=INT(IF(ISNUMBER(SEARCH("z",D2)),((LEFT(D2,10))+1),D2)+1)

This will allow you to use SUMIFS.

[DEEARO]

the formula works like a charm...however as i have changed some places to sumproduct (these places have stopped working) and some places are sumifs, is there a formula for table3 column date, which will work for both. I dont need the SEARCH("z",D2) section of the formula now, therefore i have changed the formula to =INT(LEFT(d2,10))+1, but where sumproduct is used it has stopped working

[AliGW]

No. You need to make your mind up and use one or the other. Catering for lots of inconsistencies within formulae is NOT the way to go.

I suggest you go with the date changes and convert all formulae back to SUMIFS.

But you won't be working on the original workbook, anyway, so just go back to that and introduce the new date formula. It will still have all the SUMIFS in it.

[DEEARO]

i would like to coniine using sumifs please.

i changed the following formula from
INT(IF(ISNUMBER(SEARCH("z",D2)),((LEFT(D2,10))+1),D2)+1)

in

=INT((LEFT(D2,10))+1)

and both sumproduct and simfis stopped working.

how do i ammend the formula please

[DEEARO]

this forumla not working on sheet sheet WEEK2

[AliGW]

i would like to coniine using sumifs please.

That's up to you.

I have offered you two solutions. Let us know how you get on.

Post #16 has a working workbook with the date formula and SUMIFS - use that as your starting point.

[AliGW]

Not all of the dates in the drop-down on the Week 2 sheet are whole numbers.

F1 on the drop-downs sheet would need to be this:

=INT(MAX(TABLE3[Week Commencing]))

This has fixed the Week 2 sheet.

You have, frankly, a bit of a dog's dinner going on in this workbook, with a horrifying muddle of whole number dates and dates with time parts. All dates, one way or another, need to be converted to whole numbers using INT. These are not problems caued by my formulae, rather ones that already exist in your data. Data cleansing is up to you, so you really need to go through this with a fine-toothed comb and get all of your date fields ironed out.

[DEEARO]

sorryyyyyyy for all the questions:

i have decided to stick with sumproduct.

however when i have introduced >= and <= the formula is not working.

Please see sheet average ( cell c10 to c:28) my total is incorrect.

and
Please see sheet week2( cell c224 to c:240) my total is incorrect.

[AliGW]

No, I'm sorry - ALL of your issues are to do with the dog's dinner I referred to earlier.

I am not prepared to continue mitgating your mess.

My advice: go back to SUMIFS. Go through the workbook with a fine-toothed comb and make sure that EVERY date field is returning a whole number.

This is the only way to really sort this out, and doing it for you is, I am sorry to say, way outside my (voluntary) remit.

[JohnTopley]

First error in WEEK2

Please Login or Register  to view this content.

[AliGW]

Not only that, John - there's a misplaced bracket, too:

*(INT(TABLE3[Date]>=WEEK2!$C$219))

Should be:

*(INT(TABLE3[Date])>=WEEK2!$C$219)

But as I have said, I don't believe that fixing these formulae to work with uncleansed data is the right approach: the data should be cleansed first (thoroughly).

[DEEARO]

But as I have said, I don't believe that fixing these formulae to work with uncleansed data is the right approach: the data should be cleansed first (thoroughly). i

i dont understand this. please explain

[DEEARO]

the forumla works with the extra bracket, however the total has not correct.

[AliGW]

please explain

Again?

Your workbook is full of dates. Some of them are whole numbers and some are dates with time parts. You can see this if you set formatting to general. This date has a time part:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	C	D
32	45432.95833
33
34

Sheet: WEEK2

This date is a whole number:

AliGW on MS365 Beta Channel (Windows 11) 64 bit

	C
219	45432
220
221

Sheet: WEEK2

These dates need cleaning up so that ALL dates ANYWHERE in your workbook are presenting as whole numbers. Then your SUMIFS formulae will work.

You need to do this data cleansing.

[AliGW]

the forumla works with the extra bracket, however the total has not correct.

No, it doesn't. But this should:

=SUMPRODUCT(TABLE3[No.Employers/Provide]*(INT(TABLE3[Date])>=$C$219)*(INT(TABLE3[Date])<=$E$219)*(TABLE3[JCPLocation]=$B224)*(TABLE3[TypeofEvent]="Employer"))+SUMPRODUCT(TABLE3[No.Employers/Provide]*(INT(TABLE3[Date])>=$C$219)*(INT(TABLE3[Date])<=$E$219)*(TABLE3[JCPLocation]=$B224)*(TABLE3[TypeofEvent]="jobs fair"))

I have made the changes referred to in posts #25 and #26.

I am now out.

[DEEARO]

i want to say thank you so Ali, for your help...i was tunnel vision. I took a break for few days, and then i was able to see all the mistakes i was doing.

[AliGW]

Glad to have helped.

If you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.