Formula in criteria range in SUMIFS

0 votes

Can I apply the formula in the criteria range in SUMIFS as I don't want to do it outside?

I am tried something like this:

=SUMIFS('sheet1'!D:D,(LEFT('sheet2'!A:A,6)),"="&LEFT('sheet3'!B1,6))
Sep 29, 2022 in Others by Kithuzzz
 • 38,000 points • 519 views

1 answer to this question.

0 votes

No, that kind of manipulation doesn't work in SumIfs. You would need to use SumProduct. You shouldn't use full-column references with that, since that will be very slow.

=SUMProduct('sheet1'!$D$1:$D$1000,--(LEFT('sheet2'!$A$1:$A$1000,6)=LEFT('sheet3'!B1,6)))
answered Sep 30, 2022 by narikkadan
 • 63,600 points

Related Questions In Others

0 votes
1 answer

SQL - The conversion of a varchar data type to a datetime data type resulted in an out-of-range value

 I ran into the same problem due ...READ MORE

answered Feb 23, 2022 in Others by Aditya
 • 7,680 points • 10,938 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
 • 63,600 points • 3,723 views
0 votes
1 answer

Extract unique value from the range in Excel

The portability of spreadsheet functions like UNIQUE() ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
 • 63,600 points • 1,249 views
0 votes
1 answer

Sumifs function not recognizing "true" criteria/column

You must switch to an Array Type ...READ MORE

answered Sep 29, 2022 in Others by narikkadan
 • 63,600 points • 576 views
0 votes
0 answers

MS Excel - SumProduct formula with Loop

1 I have 4 arrays of data where ...READ MORE

Feb 18, 2022 in Others by Edureka
 • 13,690 points • 422 views
0 votes
1 answer

Is there a way to SUMIFS with multiple criteria + true or false?

You can add this as the  last criteria: =SUMIFS(STORE!$C$6:$C$1000;STORE!$A$6:$A$1000;""&SUMMARY!$D$5&"";STORE!$D$6:$D$1000;""&SUMMARY!$C$9&"";STORE!$E$6:$E$1000;""&SUMMARY!D8&"";STORE!$AA$6:$AA$1000;TRUE) STORE!$AA$6:$AA$1000: ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
 • 63,600 points • 1,082 views
0 votes
1 answer

How to define excel SUMIFS criteria considering any text and numbers?

It essentially ignores column C's missing entry ...READ MORE

answered Oct 1, 2022 in Others by narikkadan
 • 63,600 points • 967 views
0 votes
1 answer

How to do the comand IF THEN in Excel

Try this: =SUM(IF(AND(H39<=38,H39>=20),1,0),... If so then maybe this is ...READ MORE

answered Mar 28, 2023 in Others by Kithuzzz
 • 38,000 points • 538 views
0 votes
1 answer

In a excel formula I need to create a list of names on one sheet based upon criteria/data of another sheet

The final formula is: =IF(ROWS($H$3:H3)<=$I$1,INDEX(Personnel! ...READ MORE

answered Nov 25, 2022 in Others by narikkadan
 • 63,600 points • 1,109 views
0 votes
1 answer

Ignore empty filter criteria in SUMPRODUCT formula

=SUMPRODUCT(($C$2:$C$7)*(IF(E1="",1,($A$2:$A$7=E1))*(IF(E2="",1,$B$2:$B$7=E2)))) Replaces the condition with 1 (all values) in case E1 and/or E2 is ...READ MORE

answered Feb 13, 2023 in Others by narikkadan
 • 63,600 points • 882 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP
"PMP®","PMI®", "PMI-ACP®" and "PMBOK®" are registered marks of the Project Management Institute, Inc. MongoDB®, Mongo and the leaf logo are the registered trademarks of MongoDB, Inc.