How to define excel SUMIFS criteria considering any text and numbers

0 votes
My SUMIFS formula's requirements are dependent on a cell (let's say A1) that has data that has been validated by a list and altered by user selection. If a cell has text or a number that can be selected from a drop-down menu, the SUMIFS formula uses that information as a criterion to determine the sum that applies. I want the formula to sum everything without any conditions if the criteria cell is left empty. My issue is that when I inserted an IF condition like SUMIFS(sum-range, criteria range,IF(A1>"",A1,"*")) into the criteria field of the SUMIFS formula, Excel only took into account text values and ignored cells with numbers. In a nutshell, I want the SUMIFS formula to sum all values, including blank cells, integers, and texts if there is no selection in A1. How do we go about doing that?
Sep 30, 2022 in Others by Kithuzzz
• 38,000 points
1,006 views

1 answer to this question.

0 votes

It essentially ignores column C's missing entry and only considers the other two. ( Although the fourth criterion in your example formula is not obvious from your list, the procedure can be expanded to include as many criteria as you may have.

=SUMPRODUCT((IF(LEN(C2),(INDEX(Lists,,1)=C2),TRUE))*(IF(LEN(C3),(INDEX(Lists,,2)=C3),TRUE))*(IF(LEN(C4),(INDEX(Lists,,3)=C4),TRUE))*SumRange)

To make things easier to comprehend, I made a named range Lists that includes your sample range E2:H10 and labelled I2:I10 as SumRange. The first column in the range is shown by INDEX(Lists,,1). It's crucial that Lists and SumRange both include the same amount of rows.

answered Oct 1, 2022 by narikkadan
• 63,600 points

Related Questions In Others

0 votes
1 answer

Yoast Seo breadcrumbs: How to add custom text to category and tag name

use this code  add_filter('wpseo_breadcrumb_single_link', 'filter_breadcrumbs_for_h1', 10, 2); function filter_breadcrumbs_for_h1($link_output, ...READ MORE

answered Feb 12, 2022 in Others by narikkadan
• 63,600 points
749 views
0 votes
1 answer

Yoast Seo breadcrumbs: How to add custom text to category and tag name

add_filter('wpseo_breadcrumb_single_link', 'filter_breadcrumbs_for_h1', 10, 2); function filter_breadcrumbs_for_h1($link_output, $link) {     if ...READ MORE

answered Feb 25, 2022 in Others by narikkadan
• 63,600 points
1,471 views
0 votes
0 answers
0 votes
1 answer
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
429 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hello, yes u can find your birthdate using ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
4,106 views
0 votes
1 answer

Calculate Birthdate from an age using y,m,d in Excel

Hi To Calculate the date, we can ...READ MORE

answered Feb 16, 2022 in Others by Edureka
• 13,690 points
2,009 views
0 votes
0 answers

Convert Rows to Columns with values in Excel using custom format

1 I having a Excel sheet with 1 ...READ MORE

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

How to format numbers as lakhs and crores in excel/ google spreadsheet when the number could be negative too?

Excel formatting, in my opinion, can only ...READ MORE

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

How to scrape the specific text from kworb and extract it as an excel file?

The best practice to scrape tables is ...READ MORE

answered Feb 18, 2023 in Others by narikkadan
• 63,600 points
794 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